Visio and ShapeSheet Formulas

Question

Hi! I am working on a Visio (Visio 2010, but I'm not sure if that makes a difference), and I have the following formulas in user-defined cells:

1. Formula that lists a bunch of values, and the values come from a shape's text. Here is the formula:

User.WeldingValues=SUBSTITUTE(SHAPETEXT(Pages[Page-6]!Sheet.906!TheText,16)," ",";")

The values are: 0;0;0;0;0.157 (list is longer, but you get the idea)

When I change the value in "Sheet.906," this list does update. So, I changed the values from "0;0;0;0;0.157" to "0;0;0;0;0.16" and User.WeldingValues did update accordingly.

2. I am then using an INDEX/LOOKUP formula to find the correct value in this list. Here is that formula:

User.ToWelding=INDEX(LOOKUP(User.ToPinTermKey,User.ZetaValues),User.WeldingValues)

(User.ZetaValues in a similar list to the above, and comes from another shape's text).

When I change the text in shape "Sheet.906," User.WeldingValues changes as stated above, but User.ToWelding does not update. Therefore, User.ToWelding should have found the 0.157 value, which it did, but it did not update to 0.16 when I changed "Sheet.906."

Why is this? Are there rules around ShapeSheet formula updates of which I am not aware?

Thank you so much in advance for any help that can be provided!

Heather

Answer 1

@Heather McIntyre

This maybe relate to LOOKUP function.
Since if the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
So it may have updated, but it meets the above conditions, the output is the same as before.
More information, you may refer to "LOOKUP function".

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.