Trouble simplifying an IF formula

[Beware Wet Paint]

Hello lovely people,

I'm trying to write an IF formula which returns a certain value if a referenced cell contains any text and a different value if it is blank.

As it stands the only way I've been able to do this is to use a formula which nests each different possible text value the cells could contain- as there are numerous text values the cells could contain the resulting formula is rather long and complex.

I was wondering if there could be a different approach or an alternate function I could use to simplify this situation. Any suggestions are gratefully received, thank you.

 

[Aladin Akyurek]

Thank you for all the replies, I've had success using =IF(ISTEXT(A1),"","Mr Jones") but I will also be trying out the list option as I think =IF(ISTEXT(A1),"","Mr Jones") will become invalid if A1 contains a formula of it's own as I think the ISTEXT function recognizes a formula as text?

why don't you try the COUNTIF formula which gives you exactly what you need?

 

[Beware Wet Paint]

Sorry, I'm very limited in my knowledge of functions but doesn't the COUNTIF function return a numerical value based on the specified criteria? I'm just thinking if I used COUNTIF it wouldn't give a text result? Forgive me if I'm incorrect, like I say my ignorance is shocking.

 

[Aladin Akyurek]

Sorry, I'm very limited in my knowledge of functions but doesn't the COUNTIF function return a numerical value based on the specified criteria? I'm just thinking if I used COUNTIF it wouldn't give a text result? Forgive me if I'm incorrect, like I say my ignorance is shocking.

Enter the following in D1:D5 one by one:

Holiday
Part
Mat leave
LTS
SVR

Now invoke:

=IF(COUNTIF($D$1:$D$5,$A1),"","Mr Jones")

Try to test the formula for

A1 empty
A1 London
A1 =""
A1 SVR

in order to observe how it behaves.

The formula is equivalent to:

=IF(COUNTIF($D$1:$D$5,$A1)>0,"","Mr Jones")

 

[Beware Wet Paint]

Thank you I shall try it and let you know how I get on- slowly getting to grips with functions.

 

[Beware Wet Paint]

Thank you this this formula worked brilliantly thank you for your time and expertise.

 

[Aladin Akyurek]

Thank you this this formula worked brilliantly thank you for your time and expertise.

You are welcome. Thanks for providing feedback.