| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I use "#N/A" return from a vlookup to change row color?
The row that returns "#N/A" from =VLOOKUP(H2,Compare!$A$2:$K$890,11,FALSE)
would help a lot by coloring the row green for non-#n/a and red for #n/a. This way I know right away that the information exists in the other speadsheet or not. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How Can I use "#N/A" return from a vlookup to change row color?
You can use Conditional Formatting for this.
First, on the Compare Sheet, select the columns from A to K and name the range as CompareRange - Insert | Name | Define | =Compare!$A:$K You have to do this because you cannot use a range in another worksheet in Conditional Formatting. Naming the range gets around this. A cheat but OK Now, select *all of row 2* on your original worksheet and select Format | Conditional Formatting from the menu. In Condition 1, set Formula is: =IF(ISNA(VLOOKUP($H2,CompareRange,11,FALSE)),FALSE ,TRUE) and set Format | Patterns to green In Condition 2, set Formula is: =IF(ISNA(VLOOKUP($H2,CompareRange,11,FALSE)),TRUE, FALSE) and set Format | Patterns to red Copy the formatting down to all the other rows. If the value in Hx is found in the table on the Compare worksheet then row x should turn green otherwise it should turn red. Regards Trevor "La" wrote in message ... The row that returns "#N/A" from =VLOOKUP(H2,Compare!$A$2:$K$890,11,FALSE) would help a lot by coloring the row green for non-#n/a and red for #n/a. This way I know right away that the information exists in the other speadsheet or not. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How Can I use "#N/A" return from a vlookup to change row color?
Trevor's reply is great but it seems a tad more complicated than it needs to
be. counitf would be a lot easier =COUNTIF(Range,Cell)<0 If it finds Cell in Range then the count < 0 and it returns true, otherwise false. Just a thought... -- HTH... Jim Thomlinson "La" wrote: The row that returns "#N/A" from =VLOOKUP(H2,Compare!$A$2:$K$890,11,FALSE) would help a lot by coloring the row green for non-#n/a and red for #n/a. This way I know right away that the information exists in the other speadsheet or not. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How Can I use "#N/A" return from a vlookup to change row color?
Fair comment. I was trying to relate directly to the formula being used.
Always good to have options though ;-) Regards Trevor "Jim Thomlinson" wrote in message ... Trevor's reply is great but it seems a tad more complicated than it needs to be. counitf would be a lot easier =COUNTIF(Range,Cell)<0 If it finds Cell in Range then the count < 0 and it returns true, otherwise false. Just a thought... -- HTH... Jim Thomlinson "La" wrote: The row that returns "#N/A" from =VLOOKUP(H2,Compare!$A$2:$K$890,11,FALSE) would help a lot by coloring the row green for non-#n/a and red for #n/a. This way I know right away that the information exists in the other speadsheet or not. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How Can I use "#N/A" return from a vlookup to change row color
I appreciate the effort - IT WORKED!
Thanks "Trevor Shuttleworth" wrote: Fair comment. I was trying to relate directly to the formula being used. Always good to have options though ;-) Regards Trevor "Jim Thomlinson" wrote in message ... Trevor's reply is great but it seems a tad more complicated than it needs to be. counitf would be a lot easier =COUNTIF(Range,Cell)<0 If it finds Cell in Range then the count < 0 and it returns true, otherwise false. Just a thought... -- HTH... Jim Thomlinson "La" wrote: The row that returns "#N/A" from =VLOOKUP(H2,Compare!$A$2:$K$890,11,FALSE) would help a lot by coloring the row green for non-#n/a and red for #n/a. This way I know right away that the information exists in the other speadsheet or not. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How Can I use "#N/A" return from a vlookup to change row color
Thanks for the feedback ... but no need to sound so surprised ;-)
"La" wrote in message ... I appreciate the effort - IT WORKED! Thanks "Trevor Shuttleworth" wrote: Fair comment. I was trying to relate directly to the formula being used. Always good to have options though ;-) Regards Trevor "Jim Thomlinson" wrote in message ... Trevor's reply is great but it seems a tad more complicated than it needs to be. counitf would be a lot easier =COUNTIF(Range,Cell)<0 If it finds Cell in Range then the count < 0 and it returns true, otherwise false. Just a thought... -- HTH... Jim Thomlinson "La" wrote: The row that returns "#N/A" from =VLOOKUP(H2,Compare!$A$2:$K$890,11,FALSE) would help a lot by coloring the row green for non-#n/a and red for #n/a. This way I know right away that the information exists in the other speadsheet or not. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup and bring the "font color" of the cell across? | Excel Worksheet Functions | |||
| correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| get the VLOOKUP function to return the "contents" of a cell? | Excel Worksheet Functions | |||
| IF(VLOOKUP("MYDATA", MYNAME, 4) = 0, "TRUE", "FALSE") | Excel Programming | |||
Linear Mode
