Have a formula where I have to find out if anything has been entered in fields R6 to R1... R6 as the most significant. If blank, the next one is checked, etc.
And have a formula and it works - almost..... But there are problems if letters are entered. (The value of R1 to R6 can be from a-z or 1-99).
My formula is like this:
=IF(SHAPETEXT(R6!TheText)<>"",SHAPETEXT(R6!TheText),IF(SHAPETEXT(R5!TheText)<>"",SHAPETEXT(R5!TheText),IF(SHAPETEXT(R4!TheText)< >"",SHAPETEXT(R4!TheText),IF(SHAPETEXT(R3!TheText)<>"",SHAPETEXT(R3!TheText),IF(SHAPETEXT(R2!TheText)<>"",SHAPETEXT(R2!TheText) ,IF(SHAPETEXT(R1!TheText)<>"",SHAPETEXT(R1!TheText),99))))))
Note. The solution must be in a formula and not in VBA/macro (macros are not allowed).
Any help or suggestions are appreciated, thanks
Welcome to the forum!
For compare strings (text) use STRSAME function (https://docs.microsoft.com/en-us/office/client-developer/visio/strsame-function)
Also, hope this thread can helps: How Many IF Functions Can Be Nested in One ShapeSheet Formula? (http://visguy.com/vgforum/index.php?topic=1882.msg8468#msg8468)
Hi Surrogate.
Thank you for replying & warm welcome.
Do you have an exsample with "IF" & "STRSAME" to valuate if anything been entered in the cell/field. And if, - returns the value regardless of whether it is text, letters or numbers.
br LunJep
In case when you check not empty string you can use LEN function (https://docs.microsoft.com/en-us/office/client-developer/visio/len-function-visioshapesheet).
Quote=IF(LEN(SHAPETEXT(R6!TheText))>0,SHAPETEXT(R6!TheText),IF(LEN(SHAPETEXT(R5!TheText))>0,SHAPETEXT(R5!TheText),IF(LEN(SHAPETEXT(R4!TheText))>0,SHAPETEXT(R4!TheText),IF(LEN(SHAPETEXT(R3!TheText))>0,SHAPETEXT(R3!TheText),IF(LEN(SHAPETEXT(R2!TheText))>0,SHAPETEXT(R2!TheText),IF(LEN(SHAPETEXT(R1!TheText))>0,SHAPETEXT(R1!TheText),99))))))
Hi,
to create a long formula like this I use Excel. All lines start with "IF(" and the changing values are in a separate column. Concatenate everything together and it's done.
Visguy used some vba for creating this - nice idea but with Excel I'm faster - what is due to lack of programming skills ;).
But his 'hack" to shorten the name of the row to just one letter is really good! I use the prompt.column to explain what happens in this row.
just my 2 cents.
hl
Long, catenated IF statements can become confusing and difficult to test validate. Generally, such problems van be avoided if each IF statement is instantiated in its own user row. That way, rather than leap frothing through some long formula, the logic progresses from one user entry to the next. Each entry is simple. Thus easily understood and editable.
All effort remains within Visio's shapesheet. No VBA. No external app.
There is potentially a case where VBA could be needed. If the shape being checked, has no text, the shapesheet reference to that shape's text will cause an error.
Not aware of any way to trap this error from within the shapesheet.
Hi again.
The LEN function worked !!!
Thanks a lot!!
=IF(LEN(SHAPETEXT(R6!TheText))>0,SHAPETEXT(R6!TheText),IF(LEN(SHAPETEXT(R5!TheText))>0,SHAPETEXT(R5!TheText),IF(LEN(SHAPETEXT(R4!TheText))>0,SHAPETEXT(R4!TheText),IF(LEN(SHAPETEXT(R3!TheText))>0,SHAPETEXT(R3!TheText),IF(LEN(SHAPETEXT(R2!TheText))>0,SHAPETEXT(R2!TheText),IF(LEN(SHAPETEXT(R1!TheText))>0,SHAPETEXT(R1!TheText),99))))))
br. LunJep