### Author Topic: ShapeSheet Row Self Reference  (Read 675 times)

0 Members and 1 Guest are viewing this topic.

#### thekingofspain

• Newbie
• Posts: 2
##### ShapeSheet Row Self Reference
« on: December 22, 2022, 01:56:11 PM »
I am trying create a generic shapesheet formula that uses the current row's index.  The theory is that the current row's index can be used to reference another row by a given offset or to calculate some magic number.

In the UML Sequence template's UML Actors shape, the Connection Points Y formulas are a series of hard coded formulas that I would to convert to a single formula that uses the current row's row index.  Thus one can just copy and paste the existing formula to a new row when an additional connection point is needed on the shape.

Existing
Row 1.Y      = IF(Controls.Row_1.Y<-6.35 mm,-6.35 mm,Controls.Row_1.Y)
Row 2.Y      = IF(Controls.Row_1.Y<-12.7 mm,-12.7 mm,Controls.Row_1.Y+MODULUS(ABS(Controls.Row_1.Y),6.35))
...
Row 100.Y  = IF(Controls.Row_1.Y<-635 mm,-635 mm,Controls.Row_1.Y+MODULUS(ABS(Controls.Row_1.Y),6.35))

To something like
Row n.Y     = IF(Controls.Row_1.Y< thisRow.Index * User.ConnectionPointWidth < thisRow.Index * User.ConnectionPointWidth, Controls.Row_1.Y+MODULUS(ABS(Controls.Row_1.Y), User.ConnectionPointWidth))

Is such a thing possible directly or indirectly via the default row name?

thanks

tkos

#### Surrogate

• Hero Member
• Posts: 1836
##### Re: ShapeSheet Row Self Reference
« Reply #1 on: December 22, 2022, 02:25:43 PM »
I see two ways:
1. using VBA code to fill in these lines (the easiest way)
2. using the CONCATENATE function in Excel, generate a long formula that will correct the formulas in these 100 cells.

#### thekingofspain

• Newbie
• Posts: 2
##### Re: ShapeSheet Row Self Reference
« Reply #2 on: December 22, 2022, 03:16:25 PM »
I just used excel as a hack to create 100 fixed formulas and then created 100 rows via in visio via menus clicks (only accidently deleted the connection points section once) and then performed a 100 copy & pastes.  However I would like to leverage the shapesheet itself going forward.