Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert State abbreviations color coded cell
I would like to take fifty state abbreviations, divide into 4 times zones
each with each zone (with multiple states) having its own color. For example, anytime Excel sees CA,NV,OR,WA it colors the cells red. If it sees TX, OK, MO, AL, WI etc it colors the cells green. Yellow for east coast, etc. The only way I could figure to do this was to convert the text to numbers then use conditional formatting but didn't see a function that would work on state abbreviations. Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert State abbreviations color coded cell
Normally you could create a lookup table with state abreviation in one column
and zone in the adjacent column. You caould then apply conditional formatting based upon a simple VLOOKUP equation. Problem is that some states are in two zones. States like: Idaho Kansas Kentucky etc. -- Gary''s Student - gsnu200760 "exceluser" wrote: I would like to take fifty state abbreviations, divide into 4 times zones each with each zone (with multiple states) having its own color. For example, anytime Excel sees CA,NV,OR,WA it colors the cells red. If it sees TX, OK, MO, AL, WI etc it colors the cells green. Yellow for east coast, etc. The only way I could figure to do this was to convert the text to numbers then use conditional formatting but didn't see a function that would work on state abbreviations. Thank you |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert State abbreviations color coded cell
Hmmm... Given that there are 6 US time zones, that 14 states have more
than one time zone, that AZ doesn't change to DST, and that *parts* of IN don't change, you may need to provide more info... For a worksheet function/CF solution, create a table of states and integers, then use VLOOKUP() in your CF. Or you could use either a Worksheet_Change() or Worksheet_Calculate() event macro In article , exceluser wrote: I would like to take fifty state abbreviations, divide into 4 times zones each with each zone (with multiple states) having its own color. For example, anytime Excel sees CA,NV,OR,WA it colors the cells red. If it sees TX, OK, MO, AL, WI etc it colors the cells green. Yellow for east coast, etc. The only way I could figure to do this was to convert the text to numbers then use conditional formatting but didn't see a function that would work on state abbreviations. Thank you |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert State abbreviations color coded cell
To expand on Gary's reply, you conditional format would be
Formula is: =IF(VLOOKUP(A1,$G$1:$I$50,3,FALSE)=1,TRUE,FALSE) Where A1 is the cell the format is appied to, and G1:I50 is your vloopup table. Also, conditional formats have a limit of 3. (annoying) You could get around this by making the default background one of the colors you want. Then the conditional format will change all the others. -- Best Regards, Luke M "Gary''s Student" wrote: Normally you could create a lookup table with state abreviation in one column and zone in the adjacent column. You caould then apply conditional formatting based upon a simple VLOOKUP equation. Problem is that some states are in two zones. States like: Idaho Kansas Kentucky etc. -- Gary''s Student - gsnu200760 "exceluser" wrote: I would like to take fifty state abbreviations, divide into 4 times zones each with each zone (with multiple states) having its own color. For example, anytime Excel sees CA,NV,OR,WA it colors the cells red. If it sees TX, OK, MO, AL, WI etc it colors the cells green. Yellow for east coast, etc. The only way I could figure to do this was to convert the text to numbers then use conditional formatting but didn't see a function that would work on state abbreviations. Thank you |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert State abbreviations color coded cell
Or just
=VLOOKUP(A1,$G$1:$I$50,3,FALSE)=1 -- Regards, Peo Sjoblom "Luke M" wrote in message ... To expand on Gary's reply, you conditional format would be Formula is: =IF(VLOOKUP(A1,$G$1:$I$50,3,FALSE)=1,TRUE,FALSE) Where A1 is the cell the format is appied to, and G1:I50 is your vloopup table. Also, conditional formats have a limit of 3. (annoying) You could get around this by making the default background one of the colors you want. Then the conditional format will change all the others. -- Best Regards, Luke M "Gary''s Student" wrote: Normally you could create a lookup table with state abreviation in one column and zone in the adjacent column. You caould then apply conditional formatting based upon a simple VLOOKUP equation. Problem is that some states are in two zones. States like: Idaho Kansas Kentucky etc. -- Gary''s Student - gsnu200760 "exceluser" wrote: I would like to take fifty state abbreviations, divide into 4 times zones each with each zone (with multiple states) having its own color. For example, anytime Excel sees CA,NV,OR,WA it colors the cells red. If it sees TX, OK, MO, AL, WI etc it colors the cells green. Yellow for east coast, etc. The only way I could figure to do this was to convert the text to numbers then use conditional formatting but didn't see a function that would work on state abbreviations. Thank you |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert State abbreviations color coded cell
Thank you!
"Gary''s Student" wrote: Normally you could create a lookup table with state abreviation in one column and zone in the adjacent column. You caould then apply conditional formatting based upon a simple VLOOKUP equation. Problem is that some states are in two zones. States like: Idaho Kansas Kentucky etc. -- Gary''s Student - gsnu200760 "exceluser" wrote: I would like to take fifty state abbreviations, divide into 4 times zones each with each zone (with multiple states) having its own color. For example, anytime Excel sees CA,NV,OR,WA it colors the cells red. If it sees TX, OK, MO, AL, WI etc it colors the cells green. Yellow for east coast, etc. The only way I could figure to do this was to convert the text to numbers then use conditional formatting but didn't see a function that would work on state abbreviations. Thank you |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert State abbreviations color coded cell
Thank you!
"Luke M" wrote: To expand on Gary's reply, you conditional format would be Formula is: =IF(VLOOKUP(A1,$G$1:$I$50,3,FALSE)=1,TRUE,FALSE) Where A1 is the cell the format is appied to, and G1:I50 is your vloopup table. Also, conditional formats have a limit of 3. (annoying) You could get around this by making the default background one of the colors you want. Then the conditional format will change all the others. -- Best Regards, Luke M "Gary''s Student" wrote: Normally you could create a lookup table with state abreviation in one column and zone in the adjacent column. You caould then apply conditional formatting based upon a simple VLOOKUP equation. Problem is that some states are in two zones. States like: Idaho Kansas Kentucky etc. -- Gary''s Student - gsnu200760 "exceluser" wrote: I would like to take fifty state abbreviations, divide into 4 times zones each with each zone (with multiple states) having its own color. For example, anytime Excel sees CA,NV,OR,WA it colors the cells red. If it sees TX, OK, MO, AL, WI etc it colors the cells green. Yellow for east coast, etc. The only way I could figure to do this was to convert the text to numbers then use conditional formatting but didn't see a function that would work on state abbreviations. Thank you |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert State abbreviations color coded cell
THanks!
"JE McGimpsey" wrote: Hmmm... Given that there are 6 US time zones, that 14 states have more than one time zone, that AZ doesn't change to DST, and that *parts* of IN don't change, you may need to provide more info... For a worksheet function/CF solution, create a table of states and integers, then use VLOOKUP() in your CF. Or you could use either a Worksheet_Change() or Worksheet_Calculate() event macro In article , exceluser wrote: I would like to take fifty state abbreviations, divide into 4 times zones each with each zone (with multiple states) having its own color. For example, anytime Excel sees CA,NV,OR,WA it colors the cells red. If it sees TX, OK, MO, AL, WI etc it colors the cells green. Yellow for east coast, etc. The only way I could figure to do this was to convert the text to numbers then use conditional formatting but didn't see a function that would work on state abbreviations. Thank you |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert State abbreviations color coded cell
THank you!
"Peo Sjoblom" wrote: Or just =VLOOKUP(A1,$G$1:$I$50,3,FALSE)=1 -- Regards, Peo Sjoblom "Luke M" wrote in message ... To expand on Gary's reply, you conditional format would be Formula is: =IF(VLOOKUP(A1,$G$1:$I$50,3,FALSE)=1,TRUE,FALSE) Where A1 is the cell the format is appied to, and G1:I50 is your vloopup table. Also, conditional formats have a limit of 3. (annoying) You could get around this by making the default background one of the colors you want. Then the conditional format will change all the others. -- Best Regards, Luke M "Gary''s Student" wrote: Normally you could create a lookup table with state abreviation in one column and zone in the adjacent column. You caould then apply conditional formatting based upon a simple VLOOKUP equation. Problem is that some states are in two zones. States like: Idaho Kansas Kentucky etc. -- Gary''s Student - gsnu200760 "exceluser" wrote: I would like to take fifty state abbreviations, divide into 4 times zones each with each zone (with multiple states) having its own color. For example, anytime Excel sees CA,NV,OR,WA it colors the cells red. If it sees TX, OK, MO, AL, WI etc it colors the cells green. Yellow for east coast, etc. The only way I could figure to do this was to convert the text to numbers then use conditional formatting but didn't see a function that would work on state abbreviations. Thank you |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert State abbreviations color coded cell
Hi Luke...i'm still trying to figure this out. If I make a lokup table
w/state abr in one collumn and zone in the adjacent what is in the third column? I noticed that the formula example below contains rows g, h and I. also, am i setting up my conditional format options based on the zone numbers 1-4 or on a range of state names contained within a zone? wasn't sure by what you meant when you said A1 is the cell the format is applied to. Is A1 the first state name of the fifty states that have yet to be color coded? Thanks "Luke M" wrote: To expand on Gary's reply, you conditional format would be Formula is: =IF(VLOOKUP(A1,$G$1:$I$50,3,FALSE)=1,TRUE,FALSE) Where A1 is the cell the format is appied to, and G1:I50 is your vloopup table. Also, conditional formats have a limit of 3. (annoying) You could get around this by making the default background one of the colors you want. Then the conditional format will change all the others. -- Best Regards, Luke M "Gary''s Student" wrote: Normally you could create a lookup table with state abreviation in one column and zone in the adjacent column. You caould then apply conditional formatting based upon a simple VLOOKUP equation. Problem is that some states are in two zones. States like: Idaho Kansas Kentucky etc. -- Gary''s Student - gsnu200760 "exceluser" wrote: I would like to take fifty state abbreviations, divide into 4 times zones each with each zone (with multiple states) having its own color. For example, anytime Excel sees CA,NV,OR,WA it colors the cells red. If it sees TX, OK, MO, AL, WI etc it colors the cells green. Yellow for east coast, etc. The only way I could figure to do this was to convert the text to numbers then use conditional formatting but didn't see a function that would work on state abbreviations. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
state abbreviations | New Users to Excel | |||
Converting State Names to State Abbreviations | Excel Discussion (Misc queries) | |||
create a list box in one cell of state abbreviations | Excel Worksheet Functions | |||
US State Abbreviations List | New Users to Excel | |||
Color Coded cell references. | Excel Discussion (Misc queries) |