Visio Guy

Visio Discussions => ShapeSheet & Smart Shapes => Topic started by: Gustavo on May 20, 2022, 01:59:36 PM

Title: Concatenate a Page cell formula to a shape cell?
Post by: Gustavo on May 20, 2022, 01:59:36 PM
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
Title: Re: Concatenate a Page cell formula to a shape cell?
Post by: wapperdude on May 20, 2022, 04:08:04 PM
Not aware of shapesheet function that will copy/transfer the literal formula from another cell.  That would require code.
Title: Re: Concatenate a Page cell formula to a shape cell?
Post by: Visisthebest on May 20, 2022, 08:32:38 PM
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.
Title: Re: Concatenate a Page cell formula to a shape cell?
Post by: Gustavo on May 21, 2022, 12:04:36 PM
Great idea Visisthebest! I'm gonna give it a shot.
Title: Re: Concatenate a Page cell formula to a shape cell?
Post by: Visisthebest on May 24, 2022, 04:53:50 AM
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!
Title: Re: Concatenate a Page cell formula to a shape cell?
Post by: Gustavo on May 25, 2022, 05:05:40 PM
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:
Code
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:
Code
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:

Code
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.



Title: Re: Concatenate a Page cell formula to a shape cell?
Post by: Visisthebest on May 26, 2022, 03:53:38 AM
Good to hear Gustavo!