Controlling multiple variables in shape data

[Michelle]

That is really close to what I am trying right now, Wapperdude.  I set a variable in the shape data for the method in which the user would like to select their item:  By part number, description or cost.  This answer determines which subsequent question the user sees in the shape data.  That is all working fine.  The issue is that behind the scenes I still need the other values to change according to what the user selects.

Here's (at least part of) my problem: 
I have the foloowing in the Shape data value for Cost:

IF(STRSAME(Prop.Row_18,"Part Number"),Scratch.B6,Scratch.B7)

and jsut to be clear, I have these formulas in scratch:

B6: IF(STRSAME(Prop.PartNumber,"EdgeVR-XCHNG-1000"),"3263",IF(STRSAME(Prop.PartNumber,"EDGEVR-XCHNG-1000R"),"3702","1080"))

B7: IF(STRSAME(Prop.Description,"EdgeVR 200 16 built-in video channels for up to 32 IP/Analog channels. Configured with a 1000 GB."),"3263",IF(STRSAME(Prop.Description,"EdgeVR 200 16 built-in video channels for up to 32 IP/Analog channels. 1000 GB of effective RAID1"),"3702","1080"))

I apologize if it's confusing, our descriptions are a bit long.  But this all works just fine.  The problem is if the user actually changes cost itself, with the pull down in Shape data the formula is wiped away.  I tried adding GUARD to protect the formula but for some reason that makes Visio see it as a circular reference. (??)  I also have had some problems with trying to fill out similar forulas for the other shape data sections.  I was getting more circular reference errors if I tried to also evaluate the description & part number shape data... 

Can you explain further your thoughts on how you would do the look up and index functions?  I have used them before, and actually had a version of this shape with them but ran into more of those %^$^* circular reference errors, so I trashed my work and started over.  I swear I must just be missing one little bit of info that will click the lightbulb on and I'll "get it".  Thanks so much for your help!!

[Michelle]

oops!  *correction*  The guard function does not cause a circular reference.  (Sorry, i think I'm just seeing these in my sleep now!) 

With the formulas in the previous example, can I add a way for the user to also select from the cost pull down? (Without messing up what is there and working?) Whenever I try, *thats* when I get circular reference.  I tried adding in a Index(0,Prop.Cost.Format) to the formula,(since that is what the cell would say if I didn't have a custom formula in it) but it doesn't work.  ANy ideas are greatly appreciated.  I feel I'm on the cusp of figuring this out... but it's just out of reach.

[wapperdude]

First, the disclaimer...I've not used Index or Lookup functions that much, so, most of what I'm suggesting I haven't tried...

I think you're pull-down list idea will work with a slight change, and avoid the circular argument.  Something like this...

1.  You have multiple lists, one for each variable.  In your example, there were 3 variables, so 3 lists.
2.  The pull-down list allows the user to select an entry.  That entry will use the lookup function on the appropriate list to get the necessary index value.
3.  That index value will then be used by for each of the 3 variables.  The index will pull the appropriate entry from each of the 3 lists using the index value.

The trick is to have the three lists, prepared ahead of time.  Generate the index from the drop-down list, which is separate, isolated from your 3 variables.  Then retrieve the corresponding entry from each list to populate the 3 variables and thus establish a customer specific list.  Thus, there is no circular argument.

HTH
Wapperdude

[Michelle]

It did help very much, Thank you!  I had a friend who is a programmer help me, and in the end we did pretty much exactly like you describe, Wapperdude.  It works beautifully!   

Thanks again!
Michelle

[wapperdude]

Very cool!