Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and Conditional Formatting
When put in the conditional formatting dialogue box, this formula
=VLOOKUP(a1,$b1:$c50,2,FALSE)=1 for box 1 w/color code red, (a1,$b1:$c50,2,FALSE)=2 for box 2 w/color code blue, (a1,$b1:$c50,2,FALSE)=3 for box 3 w/color code green will take 50 state abbreviations (singular occurence only) and color code them according to time zone. (The remainder of states have an auto background color of yellow). Right now, only one instance of each state name is being colored. How do I set this formula to work for multiple occurences of each same state name for all the 50 states? My tables usually have 10 to 20 occurences of each state each and i would like each state colored accordingly. Any help is sincerely appreciated. Thank you very much for your time. I've been at this for days!!!! Exceluser |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and Conditional Formatting
Think you need to "fix" the table array,
viz put in "$" fully, as: $b$1:$c$50 For example, your CF formula: =VLOOKUP(a1,$b1:$c50,2,FALSE)=1 should read as: =VLOOKUP(a1,$b$1:$c$50,2,FALSE)=1 Similarly for the other 2 vlookups -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "exceluser" wrote: When put in the conditional formatting dialogue box, this formula =VLOOKUP(a1,$b1:$c50,2,FALSE)=1 for box 1 w/color code red, (a1,$b1:$c50,2,FALSE)=2 for box 2 w/color code blue, (a1,$b1:$c50,2,FALSE)=3 for box 3 w/color code green will take 50 state abbreviations (singular occurence only) and color code them according to time zone. (The remainder of states have an auto background color of yellow). Right now, only one instance of each state name is being colored. How do I set this formula to work for multiple occurences of each same state name for all the 50 states? My tables usually have 10 to 20 occurences of each state each and i would like each state colored accordingly. Any help is sincerely appreciated. Thank you very much for your time. I've been at this for days!!!! Exceluser |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup & Conditional Formatting together | Excel Worksheet Functions | |||
vlookup with conditional formatting | Excel Worksheet Functions | |||
VLookup for Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional formatting on cells with a VLOOKUP formula in them | Excel Discussion (Misc queries) | |||
vlookup & conditional formatting | Excel Worksheet Functions |