Author Topic: Indirect references - is this possible in Visio?  (Read 6152 times)

0 Members and 1 Guest are viewing this topic.

alun009

  • Newbie
  • *
  • Posts: 2
Indirect references - is this possible in Visio?
« on: October 18, 2010, 04:06:02 AM »
Hi

I'm looking to try to make an indirect reference in shape data to the cell in another shape.

I have two shapes, one called GradeCosts containing the Shape Data cells (Grade1, Grade2, Grade3, etc.)

The second shape is called Process1 and contains the cells (Grade, Duration, Cost).
The idea is that the user edits the Grade and Duration cells and the Cost cell is calculated based on those values.
So,
Cost = GradeCosts!Prop.Grade3 * Prop.Duration

But when the user changes the value in Process1!Prop.Grade (say to "2"), I want the formula above to change so
Cost = GradeCosts!Prop.Grade2 * Prop.Duration

The way I would do this in Excel would be using indirect:
Cost = Indirect("GradeCosts!Prop.Grade" & Prop.Grade) * Prop.Duration

Sadly this function appear does not seem to be present in Visio 2007.  Does anybody have any bright ideas on how to do it, please?  I'm experiencing Googlefail on this issue and need a prod in the right direction.

vojo

  • Hero Member
  • *****
  • Posts: 1550
Re: Indirect references - is this possible in Visio?
« Reply #1 on: October 18, 2010, 07:58:52 AM »
if you rename a cell....reference is maintained.

If you are really looking at something like Cell 1 has the cell name for Cell 2 so that Cell 2 vs Cell 3 (either contained in Cell 1) is used for calculation in Cell 4 in some other shape.
That cant "legally" be done.   Now, I will say you can spoof this by converting the cell 1 info into text and using Setf(cell1, value)  kind of thing.    vs    Setf(getref(cell2), value)

Wont kid you...real trickly and is exposed to the problem of the names DB in visio moving things around and the reference now pointing to an entry that has moved but there is no connection to the move.
The getref function forces a names DB look up to find the entry regardless of where it moved.    I played around with defining a comm channel between 2 shapes not in a group such that one shape
could pass info to another shape not in the group and dropped arbitrarily in order.   I got it working but real obtuse and frankly, I never used for anything serious because of how tricky it gets.

If you really want to do some sort of indirection per se.   I would suggest defining a bunch of user cells at the page level (assuming not too many) and use that as a reference  (probably should save as a template)
......or......
use VBA or VBA macro for this sort of thing.   (Depending on your ambition....if in VBA could actually build your own Names DB of all cells used by indirection, then create a function to make that connection and
of course, have the entry add and entry delete functions)

Personally, this indirection limitation is one of the biggest glaring holes in Visio.    Frankly, they should have worked this vs all that container effort for 2010 since containers, in essence, are glorified groups.

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
Re: Indirect references - is this possible in Visio?
« Reply #2 on: October 18, 2010, 04:36:46 PM »
See the attached sample. It's like that, what vojo talks about, I think.

I use this trick often to control the language displayed in my shapes.
There are four shapes on a hidden background page, each containing (in shapedata) every possible word or text in my drawing in one language each.
On the main page there is a Prop.cell to switch to another language. That triggers every one of my text shapes to look for "its" text in the shape properties of the right "language shape".
Awfully complicated like vojo said, but I didn't know, how to do it otherwise.

Only other alternative besides VBA is the extensive use of If-Statements...

vojo

  • Hero Member
  • *****
  • Posts: 1550
Re: Indirect references - is this possible in Visio?
« Reply #3 on: October 19, 2010, 12:14:38 AM »
Jumpy...I dont think that is what he is talking about (a selection via index from a fixed list)

I think what he is looking for is more general than that (a value in Cell X is used to select Cell A,B,C to apply to some forumla in Cell Y)

As I understand it....text is one thing (I too use index alot for language or fill values or or).   I have not found away to use that to "dynamically" set a value in a cell so
that one shape could find another without being a group.   For example, I believe he is after something like a user putting in a shape name in Cell X (custom or user) and doing
something like setf(getref("<value in cell X>"!user.mycooldata), 294).  I managed to get a proof of concept to work...but ended up way too tricky to be usable
(basically used some Cells in the page as sort of a clearing house/conduit between independent shapes....worked but not scalable and required a good number of cells in each shape to build the comm channel).

Obviously can statically program it WHEN all shapes are laid down.....or.....use some sort of conduit shape IF all shapes involved in the group

At least that was what I thought he was after.

Alum009....if you are after a lookup from a fixed list....yes that is supported and used quite extensively throughout visio drawings.

alun009

  • Newbie
  • *
  • Posts: 2
Re: Indirect references - is this possible in Visio?
« Reply #4 on: October 19, 2010, 10:57:05 AM »
Hi

After vojo's post I investigated the setf function and I've now achieved what I wanted to do.
Jumpy's solution is very much what I wanted, and I also learned about doing a drop-down menu using the format ="Test1;Test2;Test3"

Thank you both.  It's not intuitive to have one cell changing a formula in another but it achieves exactly what I need so I am  feeling: ;D

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3881
  • Ideas Visio-lized into solutions
Re: Indirect references - is this possible in Visio?
« Reply #5 on: October 19, 2010, 03:13:04 PM »
Seems like there's a couple of ways to do this.  

If there aren't too many grade values, then, you could do use "If" statements for evaluating the cost.  Thus, IF Prop.Grade = valA, THEN cost = formula with Val1 ELSE ...etc etc.

Or, you could use Prop.Grade as an index variable, then use the index to look up the correct grade value.

Unless there are other reasons, seems this could all be done in a single shape.

Example is attached.  The Process shape uses a user.defined cell to hold the if statements and do the math.  Pushes the result into the Prop.Cost.Value cell.

HTH
Wapperdude
« Last Edit: October 19, 2010, 03:36:27 PM by wapperdude »
Visio 2019 Pro

Yacine

  • Hero Member
  • *****
  • Posts: 2715
Re: Indirect references - is this possible in Visio?
« Reply #6 on: October 20, 2010, 05:42:02 AM »
We had similar questions many times in the past.
Here's a possible solution: http://visguy.com/vgforum/index.php?topic=1714.0
Yacine