[SOLVED] ShapeSheet formulas to relate Width and User.Multiplier

[nashwaan]

Suppose we have a shape that has extra cell: User.Multiplier
The shape Width can be resized by a user by means of UI.
What ShapeSheet formulas are required in order to acheive the following:

• Whenever i resize Width via UI, i want the value of User.Multiplier to remain unchanged
• Whenever i change User.Multiplier, i want the Width to be recalculated in this fashion:

           Width{new} = (Width{old} / User.Multiplier{old}) * User.Multiplier{new}

For example, consider the following scenario: if i start with a shape that has Width = 1 in and User.Multiplier = 1

If i change User.Multiplier from 1 to 2, then i want Width to have value of 2 in. Because (1 in / 1) * 2 = 2 in
Next, If i change User.Multiplier from 2 to 3, then value of Width should be 3 in. Because (2 in / 2) * 3 = 3 in
Next, if i resize shape in UI from 3 in to 4 in, then value of User.Multiplier should remain unchanged (i.e 3)
Next, if i change User.Multiplier from 3 to 4, then value of Width should be 5.333 in. Because (4 in / 3) * 4 = 5.333 in
Next, if i resize shape in UI from 5.333 in to 4 in, then value of User.Multiplier should remain unchanged (i.e 4)
Next, if i change User.Multiplier from 4 to 2, then value of Width should be 2 in. Because (4 in / 4) * 2 = 2 in

How to acheive this in ShapeSheet? Do i need extra User cells with formulas?
Note that User.Multiplier cannot have formula, because it will be overwritten manually.

Thanks,
Yousuf.

[Jumpy]

You need a cell to store the old value of User.Multiply, User.Multiply_Old.
You need a second user defined cell, for example User.Update with the following formula:

=SETF(Getref(Width),Width/User.Multiplier_Old*User.Multiplyer)+Dependson(User.Multiply)

and finally a third, that reacts to changes of Width and saves the value of User.Multiply in User.Multiply_Old:

=SETF(GetRef(User.Multiply_Old),User.Multiply)+Dependson(Width)

I hope this works. Because it contains sth. like a circular reference I'm not 100% sure, but I trust in Visio to break that circular reference ;-)


Why Dependson? Look here:
http://msdn.microsoft.com/en-us/library/ms406681(v=office.12).aspx

[wapperdude]

This is a circular argument, and is tough to handle in a shapesheet.  But you might want to explore the setatref function.  The following link talks a little about circular arguments, and within the discussion is a link to the setatref function, with examples.

http://blogs.msdn.com/b/chhopkin/archive/2010/08/04/overwriting-cell-formulas-with-formulaforceu.aspx

HTH
Wapperdude

[nashwaan]

Thanks Jumpy,

I used your formulas and they solved the problem perfectly. 

I created extra cell User.MultiplierOld and put the following formulas in the .Prompt cells:

User.Multiplier.Prompt      =DEPENDSON(User.Multiplier)+SETF(GetRef(Width),Width/User.MultiplierOld*User.Multiplier)
User.MultiplierOld.Prompt =DEPENDSON(Width)+SETF(GetRef(User.MultiplierOld),User.Multiplier)

Thanks again, as i wouldn't figure out these by myself. 

To wapperdude,
I tried to use SETATREF/SETATREFEXPR/SETATREFEVAL formulas in different ways in Width cell to get the desired effect, but no success.
Anyways, i thank you for the guides and tips which showed me the other possibilities. 

Thanks,
Yousuf.