Help an Air Force serviceman! Stuck on a conditional formatting formula now for 4 hours... I quit, please help!

[rafiner]

Hi there,

I have a workbook with 40+ worksheets. The first tab is a Table of Contents (TOC) listing the title of each data worksheet (tabbed at bottom of spreadsheet). The remainder of worksheets are data. All I want to achieve is to turn a Table of Contents cell RED using conditional formatting once anything at all is entered in the range of cells in the data worksheet.

For example, lets say a data worksheet contains no entries in range B2:C5. I want the conditional formatting in the TOC to turn RED if anything at all is entered in the range B2:C5. So, in that range, if X is entered in C4, for example, the TOC cell turns red. Generally, people only will input 'X' here but it may be something else so would like the formula to react to any entry in those cells at all.

I understand basic conditional formatting, and thought this would be simple.... I'm hoping I'm just making it harder than it needs to be, hence I'm asking for help. I've just about gone cross-eyed using NOT(ISBLANK), ISNUMBER, and CONCATENATE formulas all to no avail. Excel geniuses, I need you!

 

[Mick Peters]

Hi There rafiner,
I am no Excel genius so I may be way off the mark on this but I do believe that conational formatting dose not work across sheets. So My advice to the solution would be to have a set of cells on the TOC sheet that are hidden away but that use a formula to work out if the cells are blank or not and place a result in that cell then use conditional formatting on the TOC page to say if that cell is isnot blank then red.
For example I used =COUNTBLANK(E1:I1) . This give a result of 5 if all the cells are blank. You can then use your conditional formatting to say if this cell <> 5 Red.
I hope this helps
Mick.

 

[Mick Peters]

Hi Again rariner,
A quick search on Conditional formatting I found the below by Shane Devenshire and It would appear that this is now possible using named ranges. So if you name each set of cells on each of the sheets differently as named ranges you should be able to do a conditional format on the TOC page referring to each of the named ranges.
http://answers.microsoft.com/en-us/profile/219c21d1-64aa-4534-a82f-d24154c5b917

The first line of you email states "Another Workbook" but I think you want between sheets, so here are the steps for that
I. here are the steps for doing it between worksheets:
Suppose the cell containing the condition for formatting is on Sheet2!A1:
1. Select Sheet2!A1 and click the Name Box on the left of the Formula Bar directly over the column A header. Type CF into the box and press Enter. (You have named this range CF).
2. Move to Sheet1!A1
3. Choose Home, Conditional Formatting, New Rule, Use a formula to determine which cell to format, and in the Format values where this formula is true enter
=CF=0
4. Click Format, Fill, pick green, click OK twice
5. Repeat step 3 entering the formula
=CF=100
6. Repeat step 4
7. Repeat step 3 using the formula
=AND(CF>0,CF<100)
Repeat step 4

I hope this helps,
Mick.