Author Topic: Concatenate a Page cell formula to a shape cell?  (Read 579 times)

0 Members and 1 Guest are viewing this topic.

Gustavo

  • Jr. Member
  • **
  • Posts: 59
  • The ultimate inspiration is the deadline.
Concatenate a Page cell formula to a shape cell?
« 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

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4331
  • Ideas Visio-lized into solutions
Re: Concatenate a Page cell formula to a shape cell?
« Reply #1 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.
Visio 2019 Pro

Visisthebest

  • Hero Member
  • *****
  • Posts: 626
Re: Concatenate a Page cell formula to a shape cell?
« Reply #2 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.
Visio 2021 Professional

Gustavo

  • Jr. Member
  • **
  • Posts: 59
  • The ultimate inspiration is the deadline.
Re: Concatenate a Page cell formula to a shape cell?
« Reply #3 on: May 21, 2022, 12:04:36 PM »
Great idea Visisthebest! I'm gonna give it a shot.

Visisthebest

  • Hero Member
  • *****
  • Posts: 626
Re: Concatenate a Page cell formula to a shape cell?
« Reply #4 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!
Visio 2021 Professional

Gustavo

  • Jr. Member
  • **
  • Posts: 59
  • The ultimate inspiration is the deadline.
Re: Concatenate a Page cell formula to a shape cell?
« Reply #5 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.




Visisthebest

  • Hero Member
  • *****
  • Posts: 626
Re: Concatenate a Page cell formula to a shape cell?
« Reply #6 on: May 26, 2022, 03:53:38 AM »
Good to hear Gustavo!
Visio 2021 Professional