Multible IF statement - not seeing letters as text

Started by LunJep, September 06, 2022, 02:53:21 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

LunJep

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

Surrogate


LunJep

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

Surrogate

In case when you check not empty string you can use LEN function.

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))))))

hidden layer

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

wapperdude

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.
Visio 2019 Pro

wapperdude

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.
Visio 2019 Pro

LunJep

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