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.