Conditional Formatting

[Andrea]

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.

 

[Gord Dibben]

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 Format>CF>Formula is =A1>5
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

 

[Debra Dalgleish]

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 Format>Conditional 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]

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

 

[Andrea]

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-----