Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional formatting...cont. from 9/25
Rowan,
That formula is real close but isn't quite working correctly. I tried it and in my table i put about 10 city abbr. Then in my named range I type "HOU" but for some reason after I did that the cells with "hou" turn red as was supposed to happen but so did the cells with "WAS", and "PIT". If I put in "DAL" into my named range the cells in my table with "DAL" turn red but so do the cells with "WAS", "PIT", and "DEN". So for some reason it is formatting the correct cells but also identifying others as needing formatting. Formula probably just needs a small correction but this formula is above my skill level. Please Help.....and Thanks!!!! -- Guenzak "Rowan" wrote: Select A1:D10 so that A1 is the activecell (should be white wiht A1 shown in the Name box on the formula bar). Then apply the conditional formatting using the formula: =NOT(ISNA(VLOOKUP(A1,Red,0))) where Red refers to your named range holding the abbreviations of cities you want coloured red. Hope this helps Rowan "Guenzak" wrote: Thanks Vac but that isn't quite doing it. I am applying the conditional format to colums ABCD rows 1 thru 10. What I applied was =Countif(H1:H10,"HOU")0 What I wanted to have happen is if "HOU" appeard in any cell H1 thru H10 then all of the cells in my main table (ABCD 1-10) that had "HOU" in it would format i'ts background to red but it isn't quite working that way. Also I have 40 different city abbr. so I have to replace "HOU" in the formula with something that would represent "anything" so that I can use any of the 40 abbr. in cells H1:H10 to change the background of the cells with those cities in the main table red. So if H1 had "CAR" H2 had "BUF" H3 had "DAL" then all of the cells in my main table that had either CAR, BUF, or DAL would format to a red background while all of the other cells with other cities remain white. you see my real table has over 400 cell with 40 different cities and a few times a week I have to highlight different cities different colors. To change the cell backgrounds manually takes hours. I want to be able to write the abbr such as "DAL" in a specified column to the right of my table (that column being H1 truH10 in the example above) and have all the cells in my 400 cell table that have "DAL" in them to change red. Thanks for any and all help!!! -- Guenzak "Vacation's Over" wrote: try countif countif(yourrow say A5:h5, abbr say "HOU")0 conditionalformat formula is - =Countif(A5:H5,"HOU")0 "Guenzak" wrote: I have done this before and can't remember the formula or figure it out again. I have a table the say is 10cells X 10 cells. In the table I want to put the abbr. for cities such as DAL, HOU, DEN. Now next to the chart I have 10 cells that I highlighted and named RED. In the past I had set up a conditional formatting formula so that if the abbr HOU in in one of the 10 cells that were called red all of the cells in the table that contained the abbr HOU would format its backround to red. It was a formula that was set up as a conditional format for the 10x10 table and in the formula it referenced the name of the group of cells that I highlighted and renamed red. Can anyone here help me with this formula? This is the same board where I found the formula a year or so ago but now I can't find it or remember it Thanks -- Guenzak |
#2
|
|||
|
|||
try
serlecting A1 for one color A2 for another color Highlite your "Red" cells in conditional just enter =$A$1 and format in second conditional =$A$2 and format "Guenzak" wrote: Rowan, That formula is real close but isn't quite working correctly. I tried it and in my table i put about 10 city abbr. Then in my named range I type "HOU" but for some reason after I did that the cells with "hou" turn red as was supposed to happen but so did the cells with "WAS", and "PIT". If I put in "DAL" into my named range the cells in my table with "DAL" turn red but so do the cells with "WAS", "PIT", and "DEN". So for some reason it is formatting the correct cells but also identifying others as needing formatting. Formula probably just needs a small correction but this formula is above my skill level. Please Help.....and Thanks!!!! -- Guenzak "Rowan" wrote: Select A1:D10 so that A1 is the activecell (should be white wiht A1 shown in the Name box on the formula bar). Then apply the conditional formatting using the formula: =NOT(ISNA(VLOOKUP(A1,Red,0))) where Red refers to your named range holding the abbreviations of cities you want coloured red. Hope this helps Rowan "Guenzak" wrote: Thanks Vac but that isn't quite doing it. I am applying the conditional format to colums ABCD rows 1 thru 10. What I applied was =Countif(H1:H10,"HOU")0 What I wanted to have happen is if "HOU" appeard in any cell H1 thru H10 then all of the cells in my main table (ABCD 1-10) that had "HOU" in it would format i'ts background to red but it isn't quite working that way. Also I have 40 different city abbr. so I have to replace "HOU" in the formula with something that would represent "anything" so that I can use any of the 40 abbr. in cells H1:H10 to change the background of the cells with those cities in the main table red. So if H1 had "CAR" H2 had "BUF" H3 had "DAL" then all of the cells in my main table that had either CAR, BUF, or DAL would format to a red background while all of the other cells with other cities remain white. you see my real table has over 400 cell with 40 different cities and a few times a week I have to highlight different cities different colors. To change the cell backgrounds manually takes hours. I want to be able to write the abbr such as "DAL" in a specified column to the right of my table (that column being H1 truH10 in the example above) and have all the cells in my 400 cell table that have "DAL" in them to change red. Thanks for any and all help!!! -- Guenzak "Vacation's Over" wrote: try countif countif(yourrow say A5:h5, abbr say "HOU")0 conditionalformat formula is - =Countif(A5:H5,"HOU")0 "Guenzak" wrote: I have done this before and can't remember the formula or figure it out again. I have a table the say is 10cells X 10 cells. In the table I want to put the abbr. for cities such as DAL, HOU, DEN. Now next to the chart I have 10 cells that I highlighted and named RED. In the past I had set up a conditional formatting formula so that if the abbr HOU in in one of the 10 cells that were called red all of the cells in the table that contained the abbr HOU would format its backround to red. It was a formula that was set up as a conditional format for the 10x10 table and in the formula it referenced the name of the group of cells that I highlighted and renamed red. Can anyone here help me with this formula? This is the same board where I found the formula a year or so ago but now I can't find it or remember it Thanks -- Guenzak |
#3
|
|||
|
|||
Thanks BJ but i don't see how that would accomplish what I'm trying to do???
Can you explain if I'm missing something? -- Guenzak "bj" wrote: try serlecting A1 for one color A2 for another color Highlite your "Red" cells in conditional just enter =$A$1 and format in second conditional =$A$2 and format "Guenzak" wrote: Rowan, That formula is real close but isn't quite working correctly. I tried it and in my table i put about 10 city abbr. Then in my named range I type "HOU" but for some reason after I did that the cells with "hou" turn red as was supposed to happen but so did the cells with "WAS", and "PIT". If I put in "DAL" into my named range the cells in my table with "DAL" turn red but so do the cells with "WAS", "PIT", and "DEN". So for some reason it is formatting the correct cells but also identifying others as needing formatting. Formula probably just needs a small correction but this formula is above my skill level. Please Help.....and Thanks!!!! -- Guenzak "Rowan" wrote: Select A1:D10 so that A1 is the activecell (should be white wiht A1 shown in the Name box on the formula bar). Then apply the conditional formatting using the formula: =NOT(ISNA(VLOOKUP(A1,Red,0))) where Red refers to your named range holding the abbreviations of cities you want coloured red. Hope this helps Rowan "Guenzak" wrote: Thanks Vac but that isn't quite doing it. I am applying the conditional format to colums ABCD rows 1 thru 10. What I applied was =Countif(H1:H10,"HOU")0 What I wanted to have happen is if "HOU" appeard in any cell H1 thru H10 then all of the cells in my main table (ABCD 1-10) that had "HOU" in it would format i'ts background to red but it isn't quite working that way. Also I have 40 different city abbr. so I have to replace "HOU" in the formula with something that would represent "anything" so that I can use any of the 40 abbr. in cells H1:H10 to change the background of the cells with those cities in the main table red. So if H1 had "CAR" H2 had "BUF" H3 had "DAL" then all of the cells in my main table that had either CAR, BUF, or DAL would format to a red background while all of the other cells with other cities remain white. you see my real table has over 400 cell with 40 different cities and a few times a week I have to highlight different cities different colors. To change the cell backgrounds manually takes hours. I want to be able to write the abbr such as "DAL" in a specified column to the right of my table (that column being H1 truH10 in the example above) and have all the cells in my 400 cell table that have "DAL" in them to change red. Thanks for any and all help!!! -- Guenzak "Vacation's Over" wrote: try countif countif(yourrow say A5:h5, abbr say "HOU")0 conditionalformat formula is - =Countif(A5:H5,"HOU")0 "Guenzak" wrote: I have done this before and can't remember the formula or figure it out again. I have a table the say is 10cells X 10 cells. In the table I want to put the abbr. for cities such as DAL, HOU, DEN. Now next to the chart I have 10 cells that I highlighted and named RED. In the past I had set up a conditional formatting formula so that if the abbr HOU in in one of the 10 cells that were called red all of the cells in the table that contained the abbr HOU would format its backround to red. It was a formula that was set up as a conditional format for the 10x10 table and in the formula it referenced the name of the group of cells that I highlighted and renamed red. Can anyone here help me with this formula? This is the same board where I found the formula a year or so ago but now I can't find it or remember it Thanks -- Guenzak |
#4
|
|||
|
|||
You aren't missing anything, I read your intial request wrong, sorry.
the equation in the conditional format should work. chck the ther range name RED is just =$H$1:$H$10 and check that h1 to h10 actula do not contain anything "Guenzak" wrote: Thanks BJ but i don't see how that would accomplish what I'm trying to do??? Can you explain if I'm missing something? -- Guenzak "bj" wrote: try serlecting A1 for one color A2 for another color Highlite your "Red" cells in conditional just enter =$A$1 and format in second conditional =$A$2 and format "Guenzak" wrote: Rowan, That formula is real close but isn't quite working correctly. I tried it and in my table i put about 10 city abbr. Then in my named range I type "HOU" but for some reason after I did that the cells with "hou" turn red as was supposed to happen but so did the cells with "WAS", and "PIT". If I put in "DAL" into my named range the cells in my table with "DAL" turn red but so do the cells with "WAS", "PIT", and "DEN". So for some reason it is formatting the correct cells but also identifying others as needing formatting. Formula probably just needs a small correction but this formula is above my skill level. Please Help.....and Thanks!!!! -- Guenzak "Rowan" wrote: Select A1:D10 so that A1 is the activecell (should be white wiht A1 shown in the Name box on the formula bar). Then apply the conditional formatting using the formula: =NOT(ISNA(VLOOKUP(A1,Red,0))) where Red refers to your named range holding the abbreviations of cities you want coloured red. Hope this helps Rowan "Guenzak" wrote: Thanks Vac but that isn't quite doing it. I am applying the conditional format to colums ABCD rows 1 thru 10. What I applied was =Countif(H1:H10,"HOU")0 What I wanted to have happen is if "HOU" appeard in any cell H1 thru H10 then all of the cells in my main table (ABCD 1-10) that had "HOU" in it would format i'ts background to red but it isn't quite working that way. Also I have 40 different city abbr. so I have to replace "HOU" in the formula with something that would represent "anything" so that I can use any of the 40 abbr. in cells H1:H10 to change the background of the cells with those cities in the main table red. So if H1 had "CAR" H2 had "BUF" H3 had "DAL" then all of the cells in my main table that had either CAR, BUF, or DAL would format to a red background while all of the other cells with other cities remain white. you see my real table has over 400 cell with 40 different cities and a few times a week I have to highlight different cities different colors. To change the cell backgrounds manually takes hours. I want to be able to write the abbr such as "DAL" in a specified column to the right of my table (that column being H1 truH10 in the example above) and have all the cells in my 400 cell table that have "DAL" in them to change red. Thanks for any and all help!!! -- Guenzak "Vacation's Over" wrote: try countif countif(yourrow say A5:h5, abbr say "HOU")0 conditionalformat formula is - =Countif(A5:H5,"HOU")0 "Guenzak" wrote: I have done this before and can't remember the formula or figure it out again. I have a table the say is 10cells X 10 cells. In the table I want to put the abbr. for cities such as DAL, HOU, DEN. Now next to the chart I have 10 cells that I highlighted and named RED. In the past I had set up a conditional formatting formula so that if the abbr HOU in in one of the 10 cells that were called red all of the cells in the table that contained the abbr HOU would format its backround to red. It was a formula that was set up as a conditional format for the 10x10 table and in the formula it referenced the name of the group of cells that I highlighted and renamed red. Can anyone here help me with this formula? This is the same board where I found the formula a year or so ago but now I can't find it or remember it Thanks -- Guenzak |
#5
|
|||
|
|||
I verified that the range named red is just H1 thru H10 and also verified
that they did not contain anything previously but the conditional formula =NOT(ISNA(VLOOKUP(A1,Red,0))) is still ging me the same problem. If I add "DAL" (for example) to one of the cells H3 (for example) all of the cells in my main table with "DAL" in then format to red but so do ones with some other city abbr in them such as "WAS" or "PIT". -- Guenzak "bj" wrote: You aren't missing anything, I read your intial request wrong, sorry. the equation in the conditional format should work. chck the ther range name RED is just =$H$1:$H$10 and check that h1 to h10 actula do not contain anything "Guenzak" wrote: Thanks BJ but i don't see how that would accomplish what I'm trying to do??? Can you explain if I'm missing something? -- Guenzak "bj" wrote: try serlecting A1 for one color A2 for another color Highlite your "Red" cells in conditional just enter =$A$1 and format in second conditional =$A$2 and format "Guenzak" wrote: Rowan, That formula is real close but isn't quite working correctly. I tried it and in my table i put about 10 city abbr. Then in my named range I type "HOU" but for some reason after I did that the cells with "hou" turn red as was supposed to happen but so did the cells with "WAS", and "PIT". If I put in "DAL" into my named range the cells in my table with "DAL" turn red but so do the cells with "WAS", "PIT", and "DEN". So for some reason it is formatting the correct cells but also identifying others as needing formatting. Formula probably just needs a small correction but this formula is above my skill level. Please Help.....and Thanks!!!! -- Guenzak "Rowan" wrote: Select A1:D10 so that A1 is the activecell (should be white wiht A1 shown in the Name box on the formula bar). Then apply the conditional formatting using the formula: =NOT(ISNA(VLOOKUP(A1,Red,0))) where Red refers to your named range holding the abbreviations of cities you want coloured red. Hope this helps Rowan "Guenzak" wrote: Thanks Vac but that isn't quite doing it. I am applying the conditional format to colums ABCD rows 1 thru 10. What I applied was =Countif(H1:H10,"HOU")0 What I wanted to have happen is if "HOU" appeard in any cell H1 thru H10 then all of the cells in my main table (ABCD 1-10) that had "HOU" in it would format i'ts background to red but it isn't quite working that way. Also I have 40 different city abbr. so I have to replace "HOU" in the formula with something that would represent "anything" so that I can use any of the 40 abbr. in cells H1:H10 to change the background of the cells with those cities in the main table red. So if H1 had "CAR" H2 had "BUF" H3 had "DAL" then all of the cells in my main table that had either CAR, BUF, or DAL would format to a red background while all of the other cells with other cities remain white. you see my real table has over 400 cell with 40 different cities and a few times a week I have to highlight different cities different colors. To change the cell backgrounds manually takes hours. I want to be able to write the abbr such as "DAL" in a specified column to the right of my table (that column being H1 truH10 in the example above) and have all the cells in my 400 cell table that have "DAL" in them to change red. Thanks for any and all help!!! -- Guenzak "Vacation's Over" wrote: try countif countif(yourrow say A5:h5, abbr say "HOU")0 conditionalformat formula is - =Countif(A5:H5,"HOU")0 "Guenzak" wrote: I have done this before and can't remember the formula or figure it out again. I have a table the say is 10cells X 10 cells. In the table I want to put the abbr. for cities such as DAL, HOU, DEN. Now next to the chart I have 10 cells that I highlighted and named RED. In the past I had set up a conditional formatting formula so that if the abbr HOU in in one of the 10 cells that were called red all of the cells in the table that contained the abbr HOU would format its backround to red. It was a formula that was set up as a conditional format for the 10x10 table and in the formula it referenced the name of the group of cells that I highlighted and renamed red. Can anyone here help me with this formula? This is the same board where I found the formula a year or so ago but now I can't find it or remember it Thanks -- Guenzak |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |