Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    conditional format with vlookup (xp)

    I search the forum for a similar question, but couldn't find one. If it's there, I apologize.

    I am using vlookup to insert data into a table. I'd like to have values > 0 as bold. However, the conditional format does not work on the vlookup data.

    If I do a copy/paste special value, it doesn't distinguish between <.05 and 5. If I do an ISNUMBER on the pasted data, it does differentiate between numbers (5) and text (.05). (??)

    Is there an easy way to have my "looked up" numbers conditionally formatted?

    Thanks.

  2. #2
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional format with vlookup (xp)

    I use conditionall formating with vlookup without problem by setting the the cell value is greater than 0 in the Conditional Formatting Dialog Box. In this order form that I use with data validation and vlookup, the user selects the item from the data validation list and the price is determined from a vlookup sheet. When the cell value is greater than 0 the conditional formatting sets a color and bolds it.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: conditional format with vlookup (xp)

    I don't have any trouble conditional format bolding a >0 result returned by =VLOOKUP().

    Your question about Paste Special | Values is a bit unclear, but if you enter

    "<.05"

    into a cell (without the quotes) Excel will assume it is text. Therefore ISNUMBER will return FALSE on the cell containing "<.5". If you were using conditional formatting to format values less than 0.5, you should use what shows in the attachment.

    If this doesn't help, perhaps you can post an example of what you mean.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: conditional format with vlookup (xp)

    Attached is a excerpted spreadsheet showing an example of my data. In the Summary Table, I have added two columns. The first is an ISNUMBER check and it appears to have logical results; e.g. <0.03 is FALSE and 18.3 is TRUE. Then I did a check using =IF(C3>0,">0",""), and that saw all items as greater than zero. I then used the ISNUMBER and >0 checks on the raw data, and found that data to produce similar results as the summary table. I added the

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: conditional format with vlookup (xp)

    If you look up "sort order" in the online help, you'll see that text values are sorted after numeric values by Excel, i.e. any text value is "greater than" any numeric value. Try the following:
    - Select C3:C13
    - Select Format | Conditional Formatting...
    - Select Formula Is from the dropdown list.
    - Enter the formula

    =AND(ISNUMBER(C3),C3>0)

    - Set the format you like (in the attached workbook, that has already been done).
    - Click OK.

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: conditional format with vlookup (xp)

    Cool.
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •