Hi all.
I want to know if its possible to concatenate a page cell formula to shape cell. I got this long formula in the page cell "User.calculation = IF(AND(Scratch.A1 = 1, Scratch.B1 =1,"0.0001254", IF(AND(Scratch.A1 = 1, Scratch.B1 =2,"0.05683")....))" etc. and I want to know if its possible to concatenate to a some shapes cell. I've tried the formula in the shapes "User.calculation = ThePage!User.calculation", but it only translate the result, not the formula. Is there a way to do it?
Best regards
Not aware of shapesheet function that will copy/transfer the literal formula from another cell. That would require code.
You can put a complex formula in to a page cell then actually evaluate this formula from a shape cell with EVALCELL() in the shape cell and ARG()'s in the page cell.
See:
https://docs.microsoft.com/en-us/office/client-developer/visio/evalcell-function
Centralizing storage of formulas in the page can be achieved this way without coding.
Great idea Visisthebest! I'm gonna give it a shot.
Let us know how you get on, here on the forum we can help you further with this if you have any follow-up questions!
The EVALCELL and ARG method worked as intended. The need behind this is to have a way to have a unique reference for a set of data in a Page cell that is useful for a bunch of shapes scattered around the page. Instead of having a long formula like:
IF(AND(Scratch.A1=X1,Scratch.B1=Y1),"Z1",IF(AND(Scratch.A1=X2,Scratch.B1=Y2)"Z2",....IF(AND(Scratch.A1=Xn,Scratch.B1=Yn),"Zn")
repeated in every shape, which could be difficult because these shapes are instances of different masters, so it would have to modify the masters, and the formula data X1,X2..Xn and Y1,Y2...Yn, varies for a different page.
Instead, I have a unique formula in the page(s) like this:
User.Lat_data=IF(AND(ARG("Lat")<=X1,ARG("Lon")=Y1),"Z1",IF(AND(ARG("Lat")=X2,ARG("Lon")=Y1),"Z2",....IF(AND(ARG("Lat")=Xn,ARG("Lon")=Yn),"Zn",0)))
User.Lon_data=IF(AND(ARG("Lat")<=X1,ARG("Lon")=Y1),"W1",IF(AND(ARG("Lat")=X2,ARG("Lon")=Y1),"W2",....IF(AND(ARG("Lat")=Xn,ARG("Lon")=Yn),"Wn",0)))
And in the shapes I got:
Scratch.A1=EVALCELL(ThePage!User.Lat_data,"Lat",PinY,"Lon",PinX)
Scratch.B1=EVALCELL(ThePage!User.Lon_data,"Lat",PinY,"Lon",PinX)
The method returns to the cell Scratch.A1 the value of "Z", and in Scratch B1 the value of "W", based on comparing the values of PinY and PinX passed as parameters to the formula in the cells ThePage!User.Lat_data and ThePage!User.Lon_data.
I'm using it to adjust the values of latitude and longitude in a Visio map, based on where the shape is placed in the page grid. And it worked well. I hope someone find it useful. Thanks all.
Good to hear Gustavo!