Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
I have a table of data in a workbook I'll call Summary.xls. The top row in the table is months. The left column is a column of categories. I have formulas in each cell to pull data from different workbooks based on the category. For example, I have a row category X which pulls data from categoryX.xls and a row category Y which pulls data from categoryY.xls. Example of some rows in categoryX.xls. Jan Feb Mar Apr May Jun Jul Number of Changes 4 2 1 6 8 7 1 Change Impact High Low High Med Low High Low So, my table in Summary.xls will pull in the 'number of changes' row, however, I want to setup conditional formatting based on the impact of the changes. If the impact is high I want the table in summary.xls to show red. So, how do I get around the fact that I can't have conditional formatting based on a value in another cell? I can't just change the color of the cell because I can move the table. That is, I can either look at Jan thru Jun or Feb thru Jul. Any help greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
On Sat, 30 Aug 2003 11:51:15 -0700, "Andrea" wrote:
So, how do I get around the fact that I can't have conditional formatting based on a value in another cell? You CAN have CF based on a value in another cell. Try this......select B1 and FormatCFFormula is =A15 Set a Format, say green background. Enter 6 in A1. B1 will turn green Enter 4 in A1. B1 will revert to no color. Gord Dibben Excel MVP XL2002 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Could you also pull the value from the Change Impact row for each
category, and store it in a hidden area of the same worksheet in Summary? Then, use conditional formatting based on the contents of the Impact cells. For example, if the CategoryX Jan Impact is in B100, and the CategoryX Jan Number of changes is in B2: 1. Select cell B2 2. Choose FormatConditional Formatting 3. Choose Formula Is 4. Enter the formula: =B100="High" 5. Click the Format button and choose the Red pattern 6. Click OK, click OK. Andrea wrote: I have a table of data in a workbook I'll call Summary.xls. The top row in the table is months. The left column is a column of categories. I have formulas in each cell to pull data from different workbooks based on the category. For example, I have a row category X which pulls data from categoryX.xls and a row category Y which pulls data from categoryY.xls. Example of some rows in categoryX.xls. Jan Feb Mar Apr May Jun Jul Number of Changes 4 2 1 6 8 7 1 Change Impact High Low High Med Low High Low So, my table in Summary.xls will pull in the 'number of changes' row, however, I want to setup conditional formatting based on the impact of the changes. If the impact is high I want the table in summary.xls to show red. So, how do I get around the fact that I can't have conditional formatting based on a value in another cell? I can't just change the color of the cell because I can move the table. That is, I can either look at Jan thru Jun or Feb thru Jul. Any help greatly appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Thank you Debra for responding so quickly.
Yes, I did think of that. Unfortunately the user could enter in two rows of the same item. That is a row that pulls data from categoryX.xls and categoryXa.xls. That would mean I need to keep track of n number of more rows. I will do this as a last result but not something I want to do for testing sake nor for maintainability. Thank you very much. Andrea -----Original Message----- Could you also pull the value from the Change Impact row for each category, and store it in a hidden area of the same worksheet in Summary? Then, use conditional formatting based on the contents of the Impact cells. For example, if the CategoryX Jan Impact is in B100, and the CategoryX Jan Number of changes is in B2: 1. Select cell B2 2. Choose FormatConditional Formatting 3. Choose Formula Is 4. Enter the formula: =B100="High" 5. Click the Format button and choose the Red pattern 6. Click OK, click OK. Andrea wrote: I have a table of data in a workbook I'll call Summary.xls. The top row in the table is months. The left column is a column of categories. I have formulas in each cell to pull data from different workbooks based on the category. For example, I have a row category X which pulls data from categoryX.xls and a row category Y which pulls data from categoryY.xls. Example of some rows in categoryX.xls. Jan Feb Mar Apr May Jun Jul Number of Changes 4 2 1 6 8 7 1 Change Impact High Low High Med Low High Low So, my table in Summary.xls will pull in the 'number of changes' row, however, I want to setup conditional formatting based on the impact of the changes. If the impact is high I want the table in summary.xls to show red. So, how do I get around the fact that I can't have conditional formatting based on a value in another cell? I can't just change the color of the cell because I can move the table. That is, I can either look at Jan thru Jun or Feb thru Jul. Any help greatly appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Hi Gord,
Thanks also for replying so quickly. I know I can have a CF based on another cell's value. But, what if that "other cell" is in another workbook. -----Original Message----- On Sat, 30 Aug 2003 11:51:15 -0700, "Andrea" wrote: So, how do I get around the fact that I can't have conditional formatting based on a value in another cell? You CAN have CF based on a value in another cell. Try this......select B1 and FormatCFFormula is =A15 Set a Format, say green background. Enter 6 in A1. B1 will turn green Enter 4 in A1. B1 will revert to no color. Gord Dibben Excel MVP XL2002 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |