About cell references
You can create interdependencies among formulas by means of ShapeSheet cell references. Cell references give you the power to calculate a value for one cell based on another cell's value. For example, a shape's Width cell might contain a formula that calculates the shape's width by referring to the value of its Height cell, so that when a user resizes the shape vertically, its width stays in proportion.
In a cell's formula, you can refer to a cell of the same shape or another object, such as a document or page, so that Microsoft Visio calculates a value for one cell based on another cell's value.
What cell references can include
Cell references can include shape identifiers (IDs) or names. You can always refer to any shape on the page by its ID, whether the shape is named or not. If a shape hasn't been named, its default name is Sheet.i, where i is the shape ID. The ID is assigned when the shape is created and does not change unless you move the shape to another page or document. If more than one shape on a page has the same name, you must include the assigned ID.
Cell reference syntax and examples
The syntax you use and whether you can refer to a shape by name depend on the relationship between the two objects. These general rules apply:
- If a shape is a peer of the shape whose formula you are editing, you can refer to the peer shape by name. If the peer shape is a group, you can refer by name to the group, but not its members. Neither can you refer by name to a shape's parent or its parent's peers.
- You can use Sheet.ID syntax to refer to any shape on the page, whether the shape is in a group or is a parent of a shape.
- Names that contain nonstandard characters must be enclosed in single quotation marks. Single quotation mark characters in a nonstandard name must be prefixed by a single quotation mark.
|
To reference a cell of |
Use this syntax |
Example |
|
The same shape |
CellName |
Width |
|
A shape, group, or guide |
Shapename!CellName |
Star!Angle |
|
A shape, group, or guide in which more than one shape at the same level has the same name |
Shapename.ID!CellName |
Executive.2!Height |
|
A named column with indexed rows |
Section.Column[index] |
Char.Font[3] |
|
An unnamed column with indexed rows |
Section.ColumnIndex |
Scratch.A5 |
|
Any shape, page, master, or style |
Sheet.ID!CellName |
Sheet.8!FillForegnd |
|
A master |
Masters[MasterName]!SheetName!CellReference |
Masters[Gear]!Shaft!Geometry1.X1 |
|
The page or master page on which the object is located |
ThePage!CellReference |
ThePage!User.Vanishing_Point |
|
Another page in the document |
Pages[PageName]!SheetName!CellReference |
Pages[Page-3]!Sheet.4!BeginX |
|
A style |
Styles!SheetName!CellReference |
Styles!Manager!LineColor |
|
The document |
TheDoc!CellReference |
TheDoc!PreviewQuality |
|
A shape, page, master, document, or style with a nonstandard name. |
'Sheetname'!CellName |
'1-D'!LineColor |
Note Visio identifies rows with numbers and columns with letters. The first two columns are X and Y, and the third to the last columns are A through Z. X and Y are skipped, and then the columns after Z are identified the same as they are in a conventional spreadsheet: AA, AB, through AZ, and so forth. These cells can be accessed only programmatically, not in a ShapeSheet window.