Conditional formating that references another page.

**wannabeexcelpro** · 05-01-2007, 05:52 PM

I use excel to monitor both my sales and my inventory on hand. Most of our items have suggested minimum quantities and i try to follow that as much as possible when writing/filling orders. However, I occasionally get merchandise returned and/or use the stock as samples both of which throw off my pretty little rounded off numbers. I would like to be able to create some type of conditional formating that would highlight the cells on my ordering page of items that are slightly above or below the 'correct, rounded' numbers so that i would know to 11 or 13, or 6 instead of 12 so as to return to the happier numbers (and facilitate month end counting).

Does anyone have a suggestion on how to get around excel not allowing you to reference other work sheets (even in the same workbook) in the conditional formating statement? Customers occasionally see the worksheet with their order on it (through email) and so i am reluctant to copy inventory amounts onto the order form...

**dominicb** · 05-01-2007, 06:18 PM

Good evening wannabeexcelpro

Does anyone have a suggestion on how to get around excel not allowing you to reference other work sheets (even in the same workbook) in the conditional formating statement?

Yes! Try this trick.
Set up an empty workbook with two sheets.
Go to Sheet 2, cell A1, and Insert > Name > Define
Make this cell a named range, say name1
Now go to Sheet 1 and in any cell go to Format > Conditional Formatting
Set the box to "Formula Is" and enter this in the formula box:
=name1=1
and set a format for it.
Now go back to Sheet 2 and set cell A1 to 1
Voila!!!

HTH

DominicB