cell qualifier if statement

hitch_hiker

Active Member
Joined
Feb 21, 2012
Messages
267
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
 

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
668
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

Active Member
Joined
Feb 21, 2012
Messages
267
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.
 

hitch_hiker

Active Member
Joined
Feb 21, 2012
Messages
267
Thanks for your help
I'd mark your reply with a thanks and a like except I have no idea how to do that
 

Forum statistics

Threads
1,081,561
Messages
5,359,611
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top