Problem with Conditional Formatting and global cell references

[korpmeis]

Hi!

I have a problem with "global" cell references and named formulas when used with conditional formatting.

I have several sheets where I use conditional formatting to highlight cells where a cell is empty.
I use the same formula for several sheets, so I thought it would be a good idea to use a Named formulas.

Let's say I name the following formula "test" in the name manager:

 = Sheet1!L9

I then set L9 to TRUE. I make a conditional format to be active when L9=TRUE thus:

=test

.

So far, so good. As long as L9 says "TRUE", the cell formats correctly.

But I want this formula to be valid for other sheets as well.
So I change the formula "test":

test=!L9

This makes the formula reference the current sheet wherever I use the formula.

BUT the conditional formatting stops working.
If I output the formula result to a cell, it returns correctly the value of L9 (in this particular case), but the conditional formatting refuses to follow.

After several attempts in a very large Workbook, I have at sometimes gotten the formulas to update, but after saving, closing and then reopening Excel the cells no longer update.

I also (sometimes) get the an error when I create the conditional formatting rule that "Conditional formatting does not allow references to other workbooks". But that is not what I'm trying to do. References to sheets works fine, but I'm not doing that either.

Does anyone know a workaround, or a problem cause?

1. I need to make around 12 formulas that rule around 4 different conditional formats. (I could make a set of those 12 formulas for each sheet, but that would give me 12*9=108 formulas to maintain).
2. I would like each formula to be able to reference each sheet without using the sheet's name.
3. I need to be able to use those formulas to govern conditional formatting directly.

Any suggestions?

Your time is much appreciated!

/Anders