Conditional Formatting #N/A
I have a VLOOKUP looking up a value on another sheet. I works fine but when
it can't find the value it shows a #N/A. I don't want to see the #N/A in any of the feilds on this sheet and I know I need to do a conditional format and turn the text to the same color as the backgroud color. I just don't know the correct "Formula Is" statement. -- Jman1018 |
Conditional Formatting #N/A
With A1 the activecell, you could use:
=isna(a1) You may want to modify your =vlookup() formula, too: =if(isna(vlookup(...)),"",vlookup(...)) Jman1018 wrote: I have a VLOOKUP looking up a value on another sheet. I works fine but when it can't find the value it shows a #N/A. I don't want to see the #N/A in any of the feilds on this sheet and I know I need to do a conditional format and turn the text to the same color as the backgroud color. I just don't know the correct "Formula Is" statement. -- Jman1018 -- Dave Peterson |
Conditional Formatting #N/A
You could just change your formula.
=IF(ISNA(VLOOKUP(...,...,...,FALSE)),"",VLOOKUP(.. .,...,...,FALSE)) -- ** John C ** "Jman1018" wrote: I have a VLOOKUP looking up a value on another sheet. I works fine but when it can't find the value it shows a #N/A. I don't want to see the #N/A in any of the feilds on this sheet and I know I need to do a conditional format and turn the text to the same color as the backgroud color. I just don't know the correct "Formula Is" statement. -- Jman1018 |
Conditional Formatting #N/A
In the Conditional Formatting dialog, change "Cell Value Is" to
"Formula Is" and use a formula like the following and select your formatting options. =IF(ISERROR(E4),IF(ERROR.TYPE(E4)=7,TRUE,FALSE),FA LSE) The formula tests if cell E4 has an error and if so, then tests whether it is a #N/A error (type = 7). Note that you do need the ISERROR function as shown. The ERROR.TYPE function itself returns an error if the referenced cell does not contain an error. Change the references to E4 to the appropriate cell. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 24 Oct 2008 13:02:01 -0700, Jman1018 wrote: I have a VLOOKUP looking up a value on another sheet. I works fine but when it can't find the value it shows a #N/A. I don't want to see the #N/A in any of the feilds on this sheet and I know I need to do a conditional format and turn the text to the same color as the backgroud color. I just don't know the correct "Formula Is" statement. |
Conditional Formatting #N/A
Works great! Thank you.
-- Jman1018 "John C" wrote: You could just change your formula. =IF(ISNA(VLOOKUP(...,...,...,FALSE)),"",VLOOKUP(.. .,...,...,FALSE)) -- ** John C ** "Jman1018" wrote: I have a VLOOKUP looking up a value on another sheet. I works fine but when it can't find the value it shows a #N/A. I don't want to see the #N/A in any of the feilds on this sheet and I know I need to do a conditional format and turn the text to the same color as the backgroud color. I just don't know the correct "Formula Is" statement. -- Jman1018 |
Conditional Formatting #N/A
Hi,
If you are using 2007 a better formula would be =IFERROR(VLOOKUP(A1,C1:N100,3,0),"") Not only is it shorter but it uses less computer power, so it runs faster. A short conditional format would be to choose Formula is and enter =ISNA(B1) and choose a font color of white. This approach has somewhat limited usefulness because if the cell is formatted with a background you need to adjust the format depending on the cell fill color. -- Thanks, Shane Devenshire "Jman1018" wrote: I have a VLOOKUP looking up a value on another sheet. I works fine but when it can't find the value it shows a #N/A. I don't want to see the #N/A in any of the feilds on this sheet and I know I need to do a conditional format and turn the text to the same color as the backgroud color. I just don't know the correct "Formula Is" statement. -- Jman1018 |
All times are GMT +1. The time now is 03:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com