cell qualifier if statement

[hitch_hiker]

G'day,
I normally use a cell qualifier =if(a3,"yes",2) this is simplified, usually the "yes" component will be a function, the purpose of this is to tidy up the display, only if data exists in cell a3 will the cell display the result of a function, this removes the unsightly column of "0" in a display sheet, typically this would be copied down to the end of the range, this system has worked for me for many spreadsheets over many years and many versions of excel. In fact one workbook I use every day has =IF(C10,VLOOKUP('ORDER INPUT'!C10,'product list'!B:M,5,FALSE),"") and it works a charm, but when I type the qualifier in I get #value if true and "2" if the cell is empty, any suggestions as to what I'm doing wrong, I'm using office 365

 

[Legacy 143009]

I am not sure if this is all about but you are checking the empty cell in a deficient way. Try:

=IF(TRIM(A1)<>"","yes",2)

 

[hitch_hiker]

I am not sure if this is all about but you are checking the empty cell in a deficient way. Try:

=IF(TRIM(A1)<>"","yes",2)

Thanks Flashbond,
It worked a treat, though I'm still at a loss as to why the cell qualifier I have always used was returning an error, and for that matter why yours worked, I thought the trim function in this case was just making it more complicated.

 

[Legacy 143009]

Actually trim prevents false positive if there is only space character.

 

[hitch_hiker]

Thanks for your help
I'd mark your reply with a thanks and a like except I have no idea how to do that

 

[jtakw]

Thanks for your help
I'd mark your reply with a thanks and a like except I have no idea how to do that

It's at the lower left corner of the post you want to "Thank" and/or "Like", just click on them.