1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Can't conditional format when cell value=0

Discussion in 'Business Applications' started by piamik2, Apr 19, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. piamik2

    piamik2 Thread Starter

    Joined:
    Mar 30, 2005
    Messages:
    120
    I would like to have cells with a value of zero, hide the zero, and my plan was to do a conditional format to make the font no color if the cell value = 0. That wouldn't work. The zero remained in black. So, I tried to have the background color be white to hide the zero, but that didn't work either.

    The reason I've got this zero showing is that I'm using vlookup to fill the cell. The formula is as follows:

    =IF(ISNA(VLOOKUP($D$1,$A$127:E141,5,FALSE)),"0",VLOOKUP($D$1,$A$127:E141,5,FALSE))


    I need to change the cell value from #N/A to zero because the cell is part of a column of numbers that I need to add so I need everything to be numbers.

    While the worksheet works fine with the zeros showing, it's not consistent with the way the rest of the sheet looks, and I want it to look good, so if someone can tell me how to fix this I'd really appreciate it.

    Also, if it makes any difference, I've got the same =if(isna(vlookup ... statement as data validation for the cell to ensure that no one can type anything in there--I want only data from the vlookup input into the cell.

    Thanks for any and all suggestions.
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    =IF(ISNA(VLOOKUP($D$1,$A$127:E141,5,FALSE)),"",VLOOKUP($D$1,$A$127:E141,5,FALSE))

    Or:

    =IF(ISNA(VLOOKUP($D$1,$A$127:E141,5,FALSE)),0,VLOOKUP($D$1,$A$127:E141,5,FALSE))
     
  3. piamik2

    piamik2 Thread Starter

    Joined:
    Mar 30, 2005
    Messages:
    120
    OK, I feel stupid.

    Why does putting the zero in quotes mess everything up?

    Anyway, thanks again for your help. Still working on improving my "scout thing." Just can't leave well enough alone. There's always room to improve, and learn more.
     
  4. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    why not TOOLS\OPTIONS\VIEW and uncheck SHOW ZERO VALUES?

    You made it a text ZERO - not the number ZERO.


    "" would make it "nothing" - hence a blank cell.

    - Castleheart
     
  5. piamik2

    piamik2 Thread Starter

    Joined:
    Mar 30, 2005
    Messages:
    120
    Thanks for the instruction. I've learned a lot for you folks.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/354415

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice