News:

BB code in posts seems to be working again!
I haven't turned on every single tag, so please let me know if there are any that are used/needed but not activated.

Main Menu

Concatenate a Page cell formula to a shape cell?

Started by Gustavo, May 20, 2022, 06:59:36 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Gustavo

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

wapperdude

Not aware of shapesheet function that will copy/transfer the literal formula from another cell.  That would require code.
Visio 2019 Pro

Visisthebest

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.
Visio 2021 Professional

Gustavo

Great idea Visisthebest! I'm gonna give it a shot.

Visisthebest

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!
Visio 2021 Professional

Gustavo

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.




Visisthebest

Visio 2021 Professional

Browser ID: smf (possibly_robot)
Templates: 4: index (default), Display (default), GenericControls (default), GenericControls (default).
Sub templates: 6: init, html_above, body_above, main, body_below, html_below.
Language files: 4: index+Modifications.english (default), Post.english (default), Editor.english (default), Drafts.english (default).
Style sheets: 4: index.css, attachments.css, jquery.sceditor.css, responsive.css.
Hooks called: 253 (show)
Files included: 34 - 1306KB. (show)
Memory used: 1104KB.
Tokens: post-login.
Cache hits: 13: 0.00280s for 26,733 bytes (show)
Cache misses: 2: (show)
Queries used: 16.

[Show Queries]