Author Topic: Multible IF statement - not seeing letters as text  (Read 447 times)

0 Members and 1 Guest are viewing this topic.

LunJep

  • Jr. Member
  • **
  • Posts: 11
Multible IF statement - not seeing letters as text
« on: September 06, 2022, 09:53:21 AM »
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
« Last Edit: September 06, 2022, 09:56:34 AM by LunJep »

Surrogate

  • Hero Member
  • *****
  • Posts: 1636
    • ShapeSheet™ Knowledge Base
Re: Multible IF statement - not seeing letters as text
« Reply #1 on: September 06, 2022, 10:03:13 AM »
Welcome to the forum!

For compare strings (text) use STRSAME function

Also, hope this thread can helps: How Many IF Functions Can Be Nested in One ShapeSheet Formula?

LunJep

  • Jr. Member
  • **
  • Posts: 11
Re: Multible IF statement - not seeing letters as text
« Reply #2 on: September 06, 2022, 10:34:00 AM »
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

  • Hero Member
  • *****
  • Posts: 1636
    • ShapeSheet™ Knowledge Base
Re: Multible IF statement - not seeing letters as text
« Reply #3 on: September 06, 2022, 11:18:56 AM »
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

  • Full Member
  • ***
  • Posts: 89
Re: Multible IF statement - not seeing letters as text
« Reply #4 on: September 06, 2022, 12:40:41 PM »
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4477
  • Ideas Visio-lized into solutions
Re: Multible IF statement - not seeing letters as text
« Reply #5 on: September 06, 2022, 01:00:46 PM »
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4477
  • Ideas Visio-lized into solutions
Re: Multible IF statement - not seeing letters as text
« Reply #6 on: September 06, 2022, 06:19:17 PM »
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

  • Jr. Member
  • **
  • Posts: 11
Re: Multible IF statement - not seeing letters as text
« Reply #7 on: September 06, 2022, 11:55:26 PM »
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