Problem with Page to page field access

[geoffg41]

Hi All

I have a problem with the following statement or to be precise the last part " FORMAT(Pages[1545_WED_SUB]!Sheet.467!User.Value ,"0.0##")&"?","")" for what ever reason I cannot get this part to work it comes up with a (Error in formula) I know that it is accessing the right shape on the right page as I put a test item in there and it showed up in the selection area

Its obvious I am doing something wrong  can anyone point me in the right direction 

=IF(STRSAME("Yes",Sheet.59653!Prop.Row_20),"Max FLI: "&FORMAT(Sheet.59654!User.FLI,"0.0##")&"?","")&IF(STRSAME("Yes",Sheet.59653!Prop.Row_21),IF(STRSAME("Yes",Sheet.59653!Prop.Row_20),CHAR(10),"")&"Calculated FLI: "&FORMAT(Sheet.59654!User.ImpedanceUpstream,"0.0##")&"?","")&IF(STRSAME("Yes",Sheet.59653!Prop.Row_19),IF(STRSAME("Yes",Sheet.59653!Prop.Row_22),CHAR(10),"")&"Calculated Load: "&FORMAT(Pages[1545_WED_SUB]!Sheet.467!User.Value ,"0.0##")&"?","")

[Surrogate]

Hi,

Did you rename pages in this documen more than one time?
https://techcommunity.microsoft.com/t5/visio-blogs-archive/name-and-nameu/ba-p/237259

Read more about local and universal names (Name/NameU) problem!

May this confusion come from Name and NameU?
See, each shape has two names. One you can see in the UI (in the "Shape Name" window for example or the drawing explorer).

Another one (NameU) is hidden. But when you name a shape for the first time, using the dialog, both names are set.
But later on, if you modify the "public" name, the "hidden" NameU is not modified.

Note that in the ShapeSheet formulas, you can only use the "hidden" NameU to refer to a shape.

[geoffg41]

Hi Thanks for the reply

Yes i did is there a way around this or is there a way of finding out its current name

Thanks

[Surrogate]

is there a way of finding out its current name

yes, you can.
Use PAGENAME or NAME function with argument 750, like PAGENAME(750)

[geoffg41]

Hi There

I have confirmed the Page Names are correct but am still having no success in getting the values I require  I have tried accessing the page User.Value by itself not in the previous formula and i am still having the same issue "=Pages[2225SUB]!Sheet.467!User.Value " what I done understand is that when I write the formula it finds the user name so I presume that it has access to the page information is there anything else that it could be

Thanks   

[Surrogate]

I have confirmed the Page Names are correct but am still having no success in getting the values I require  I have tried accessing the page User.Value by itself not in the previous formula and i am still having the same issue "=Pages[2225SUB]!Sheet.467!User.Value " what I done understand is that when I write the formula it finds the user name so I presume that it has access to the page information is there anything else that it could be

I see space at last position in selected formula ! It is OK ? Also i intrested in which position of formula you get error ?
My advice dont use same long formulas. I usually don't try to write such a long formula on one line.
break it up into parts, and see if the result is different from what I expect.

User.Part1	=IF(STRSAME("Yes",Sheet.59653!Prop.Row_20),"Max FLI: "&FORMAT(Sheet.59654!User.FLI,"0.0##")&"?","")
User.Part2	=IF(STRSAME("Yes",Sheet.59653!Prop.Row_21),IF(STRSAME("Yes",Sheet.59653!Prop.Row_20),CHAR(10),"")&"Calculated FLI: "&FORMAT(Sheet.59654!User.ImpedanceUpstream,"0.0##")&"?","")
User.Part3	=IF(STRSAME("Yes",Sheet.59653!Prop.Row_19),IF(STRSAME("Yes",Sheet.59653!Prop.Row_22),CHAR(10),"")&"Calculated Load: "&FORMAT(Pages[1545_WED_SUB]!Sheet.467!User.Value ,"0.0##")&"?","")
User.Full	=User.Part1&User.Part2&User.Part3

[geoffg41]

Thanks for the reply I copy your suggestion and the problem still exists
below is the highlighted area that creates a error in formula

(Pages[1545_WED_SUB]!Sheet.467!User.Value ,"0.0##")&"?","")

I'm not sure what else to do

[wapperdude]

To facilitate finding the error, create a new User row and copy paste your formula that seems to be failing...FORMAT(Pages[1545_WED_SUB]!Sheet.467!User.Value ,"0.0##")

That should execute immediately.  If there's an error you ought to be able to isolate it quickly.

Could be: the page name
Could be wrong shape
Could be the User row or value...maybe doesn't exist???

[geoffg41]

Thanks again this is driving me insane

So I have created a separate User.TotalLoad in the Site Supply Page in shape ID 59716 which I have placed the formula =FORMAT(Pages[1545_WED_SUB]!Sheet.467!User.Value ,"0.0##") i have checked the 1545_WED_SUB page  shape ID467 to ensure there is data available
But I still get the same error in formula

[Surrogate]

@geoffg41, can you share your document?
PS

shape ID467 to ensure there is data available

What about value in this cell ? There is string, number or date ? If not number you can get error with function FORMAT!
PPS
At your side Pages[1545_WED_SUB]!Sheet.467!User.Value get error ?

[wapperdude]

Actually, you've made progress.  You nailed the down the formula that's the issue.  I suspect it's the page name.

So, try doing this, copy the shape in question to a new page.  Leave the page name unchanged.  Say Page-147, or whatever it is.  The shape probably is sheet.1 on this page.  Now change your formula for the new page and shape.  I'd expect it to work.  If so, then the issue is with the page name.  If not, which would be really weird, then the error would seem to be the shape.  Perhaps create a new shape with a User.Value entry, and see if that works.  If not, there may be some corruption, and as Surrogate suggests, and upload would be in order.

[Surrogate]

May be this trick can help !

1.  Select shape that name you need to know
2.   Press keys Alt+F11
2a. If in VBA editor you can't see Immediate Window, press Ctrl+G and press Enter
3.  In Immediate Window paste this code

Code Select Expand

?"Pages[" & ActiveWindow.Selection(1).Parent.NameU & "]!sheet." & ActiveWindow.Selection(1).ID
Profit: in second line in Immediate Window you get full reference to selected shape

I create workbook with prepared formulas in cells B2 and B5 !
1. Select shape that name you need to know (Visio Application)
2. Open this workbook
3. If you need get reference to shape at another shape, write cell name in range A2 of workbook.
    If you need get reference to shape at same shape, write cell name in range A5 of workbook.
4. If you need get reference to shape at another shape, select cell range B2 of workbook.
    If you need get reference to shape at same shape, select cell range B5 of workbook.
And copy it.
5. Activate Visio application
6. Open VBA editor / if Immediate window is not activated press keys Ctrl+G
7. Paste formula to Immediate window and press key Enter

in immediate window in second line you can get reference to cell

[geoffg41]

First of all I would like to thankyou guys for all your help I eventually worked it out it was looking for the universal name not the page name so that fixed that problem  thanks again

Would you be able to help out the the following problem
1.  I would like to to insert the page name from the shape data list into the the page name area i have created a "User.PageValue" which is where i can get the value  is this possible
     "=Pages[2225SUB]!Sheet.1855!User.Value"

[Surrogate]

Hi, geoffg41!

You have Shape Data with type "Fixed list" or "Variable list" there stored names (universal names) of pages. And want get according value after change value in Shape Data?

[geoffg41]

Hi There
I have a fixed list in the shape data and I want to be able to put the value selected into the  page name  "=Pages[2225SUB]!Sheet.1855!User.Value"