If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional formatting on cells with a VLOOKUP formula in them
I have a worksheet with some cells that contain values generated from a
VLOOKUP formula. Some of these cells contain a #N/A error. (That's not the problem.) I want to be able to use conditional formatting to turn the cells containing #N/A red, but for some reason I can't make it work! Even on cells that have values instead, a conditional format just won't work. Is this because I'm trying to do it on VLOOKUPs? Is this an Excel glitch, or is there something special I have to do? Any help would be greatly appreciated! Thanks, JenniM |
#2
|
|||
|
|||
In CF, choose "Formula Is" and use:
=ISNA(INDIRECT("rc",0)) HTH Jason Atlanta, GA -----Original Message----- I have a worksheet with some cells that contain values generated from a VLOOKUP formula. Some of these cells contain a #N/A error. (That's not the problem.) I want to be able to use conditional formatting to turn the cells containing #N/A red, but for some reason I can't make it work! Even on cells that have values instead, a conditional format just won't work. Is this because I'm trying to do it on VLOOKUPs? Is this an Excel glitch, or is there something special I have to do? Any help would be greatly appreciated! Thanks, JenniM . |
#3
|
|||
|
|||
Hi Jenni
select the range you want to apply the conditional formatting to, choose format /conditional formatting choose formula is type =ISERROR($M2) where M2 is the first cell in the selected range click on format - set your formatting click OK twice -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "JenniM" wrote in message ... I have a worksheet with some cells that contain values generated from a VLOOKUP formula. Some of these cells contain a #N/A error. (That's not the problem.) I want to be able to use conditional formatting to turn the cells containing #N/A red, but for some reason I can't make it work! Even on cells that have values instead, a conditional format just won't work. Is this because I'm trying to do it on VLOOKUPs? Is this an Excel glitch, or is there something special I have to do? Any help would be greatly appreciated! Thanks, JenniM |
#4
|
|||
|
|||
Hi Jenni
sorry, instead of ISERROR use ISNA -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "JulieD" wrote in message ... Hi Jenni select the range you want to apply the conditional formatting to, choose format /conditional formatting choose formula is type =ISERROR($M2) where M2 is the first cell in the selected range click on format - set your formatting click OK twice -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "JenniM" wrote in message ... I have a worksheet with some cells that contain values generated from a VLOOKUP formula. Some of these cells contain a #N/A error. (That's not the problem.) I want to be able to use conditional formatting to turn the cells containing #N/A red, but for some reason I can't make it work! Even on cells that have values instead, a conditional format just won't work. Is this because I'm trying to do it on VLOOKUPs? Is this an Excel glitch, or is there something special I have to do? Any help would be greatly appreciated! Thanks, JenniM |
#5
|
|||
|
|||
Julie and Jason, thanks a million!
It works like a charm, and now I don't have to dread the rest of my afternoon! "JulieD" wrote: Hi Jenni sorry, instead of ISERROR use ISNA -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "JulieD" wrote in message ... Hi Jenni select the range you want to apply the conditional formatting to, choose format /conditional formatting choose formula is type =ISERROR($M2) where M2 is the first cell in the selected range click on format - set your formatting click OK twice -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "JenniM" wrote in message ... I have a worksheet with some cells that contain values generated from a VLOOKUP formula. Some of these cells contain a #N/A error. (That's not the problem.) I want to be able to use conditional formatting to turn the cells containing #N/A red, but for some reason I can't make it work! Even on cells that have values instead, a conditional format just won't work. Is this because I'm trying to do it on VLOOKUPs? Is this an Excel glitch, or is there something special I have to do? Any help would be greatly appreciated! Thanks, JenniM |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional Formatting on multiple cells | Karyn | General Discussion | 1 | September 16th, 2004 01:18 AM |
Required Field Conditional | Katherine R | New Users | 2 | September 1st, 2004 05:52 AM |
Conditional Formatting to Check if the Sum of a Range of Cells is Less Then 20 | Minitman | Worksheet Functions | 2 | June 8th, 2004 02:37 AM |
Conditional Formatting Questions | Andy B | Worksheet Functions | 1 | May 12th, 2004 02:48 PM |
Conditional Formatting & Blank Cells | Dominique Feteau | Worksheet Functions | 5 | February 25th, 2004 11:41 PM |