what is wrong with this formula?

Started by jebuxx, January 28, 2015, 10:47:36 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

jebuxx

Hello again,
I have a template on page 1.1 and a summary page on 1.0
On the summary page I need a sheet to display a Hello when this takes place

IF(Pages[1.1]!Sheet.245!PinX>(Pages[1.1]!Sheet.2!PinX,"Hello","")

When I try this I receive this error notice

An error (318) occurred during the action Set Formula or Name.
Referenced cell Sheet.245!PinX does not exist.

Crazy because Sheet 245 does indeed have a Pinx

any ideas?

JuneTheSecond

Best Regards,

Junichi Yoda
http://june.minibird.jp/

wapperdude

Not sure 1.1 is a valid name, but, assuming it is, it may be necessary to put it inside single quotes.  Plus, there is another error.  Try this, with or without the quotes:

IF(pages['1.1']!sheet.245!pinx>pages['1.1']!sheet.2!pinx,"Hello","")

Visio will correct the capitalisation if there are no errors.  The double quotes are necessary.

Wapperdude
Visio 2019 Pro

jebuxx

Yes I named a tab Page 1.1 - I have other tabs named as 1.2 - 1.3 - 1.4 and so on and have had no issues with this naming convention.
I did try the Formula as recommended Wapperdude and it is not a valid formula. As I mentioned I have this same Formula working with
other sheets and pages so this is why I am puzzled. I attached PDFs of both pages. On the Data page PDF I have a description on the left
of the issue. I tried to post the actual working Visio file but it is much to large. I would sure like to know what error (318) is.
thanx again for the help.

wapperdude

Ah.  So point of clarification, when you say page 1.1, that is the literal name of the page:  "1.1" without the quotes.  My misunderstanding, I took the tab name to be "Page 1.1", without the quotes.  Either way, 1.1 is, indeed, an acceptable syntax.

I guess, you're also saying, that you hand-typed the formulas into the post, because the posted formula has an extra "(" just before Pages[1.1]!Sheet.2!.

Can you verify that the actual formula doesn't have the "typo"?  If sheet.245 does indeed exist on page "1.1", then, as you say, the formula ought to work.

Wapperdude
Visio 2019 Pro

sjenks

Hi there. I work with the guy that started this post and I'm the one that actually created the visio document that is having this error.  He did mistype the formula in the beginning of this post, but I assure you that the formula is correct when we are typing it in visio.  The formula is IF(Pages[1.1]!Sheet.245!PinX>Pages[1.1]!Sheet.2!PinX,"Hello","").  It works if I use the Formula IF(Pages[1.3]!Sheet.245!PinX>Pages[1.3]!Sheet.2!PinX,"Hello","").  The problem lies with just Tab (or Page) 1.1. 

wapperdude

Hi sjenks...

OK.  That clears that up.  Since we don't have the file to look at, just trying to eliminate the obvious, so, my apologies in advance if the questions seem, uh, impertinent, no offense intended.

The obvious question would be, on page 1.1, there is indeed, two shapes which are sheet.245 and sheet.2.  That would be the nice, easy solution, because, after that, seems like there might be some sort of corruption or labeling error.

BTW, which version of Visio? 

Presumably, the formula involving the IF statement is in a part, not on page 1.1.  If you open the shapesheet of that part, and say insert the User section, if it doesn't already exist, then, enter a reduced formula, e.g., Pages[1.1]!Sheet.245!PinX.  That should (a) create no error, and (b) return the PinX value.  If there is an error, then it seems that there is a referencing error to Sheet.245.  Either, the page name is incorrect or shape name is incorrect.

You need to sleuth it out.  Various things to try.  Go to page 1.1:
   1.)  With the Size and Position Window. open, select the shape in question.  Does S&P list the shapename as expected?
   2.)  Was the shape ever renamed?  If so, then, Visio may not recognize it as Sheet.245.  To check, with shape selected, got Menu>Format>Special...  The pop-up menu will show various ID's for the shape.
   3.)  Add a new, simple shape to the page.  Note it's name, e.g., sheet.xyz.  Now, go to the page and shapesheet where you tried to enter the simple formula.  Do the same, but point to the new shape.  Does that work?  If so, then, the problem is with your Sheet.245 on page 1.1.  If not, would seem that the problem is with page 1.1 itself.

Anyway, the point is to do simple tests and checks to convince yourself that everything is as it seems.  The basic construct of the IF statement is correct, then, something within the IF statement is not.

HTH
Wapperdude.   

   
Visio 2019 Pro

sjenks

Hi.  It's Visio 2010.  I have tried it with more than just Sheet.245 so it's not that.  The formula works just fine if it is on the actual page 1.1. It's when I type the formula in the "Data" Page (Data is the actual page name that the formula would be entered on...if it worked) that it doesn't work..   Thanks for all your advice.  All those things you have mentioned I have tried, but I think unfortunately the file was corrupted somehow and it's affected the relation between Page 1.1 and the Data Page.  Lame.

wapperdude

From what you say, I think your conclusion is correct.

Nice to see you've been so thorough.

Wapperdude
Visio 2019 Pro