Formula based conditional formatting across multiple sheets

Hi all,

I need help with this conditional formatting rule-

SUM(COUNTIF('CZ-Jan'B9,"X"),COUNTIF('CS-Jan'!B9,"X"),COUNTIF('ML-Jan'!B9,"X"),COUNTIF('JA-Jan'!B9,"X"),COUNTIF('AH-Jan'!B9,"X"),COUNTIF('JE-Jan'!B9,"X"))>3

Currently, when I copy it into sheet 'CZ-Jan', It drops the sheet reference when I hit OK. So the formula changes to-

SUM(COUNTIF(B9,"X"),COUNTIF('CS-Jan'!B9,"X"),COUNTIF('ML-Jan'!B9,"X"),COUNTIF('JA-Jan'!B9,"X"),COUNTIF('AH-Jan'!B9,"X"),COUNTIF('JE-Jan'!B9,"X"))>3

When I copy the sheet amd paste the conditional formatting to the next sheet using the copy format option, the formula changes to-

SUM(COUNTIF(B9,"X"),COUNTIF(B9,"X"),COUNTIF('ML-Jan'!B9,"X"),COUNTIF('JA-Jan'!B9,"X"),COUNTIF('AH-Jan'!B9,"X"),COUNTIF('JE-Jan'!B9,"X"))>3

This leads to errors, so I am having to paste the formula individually into each sheet.

Is there any way around this whereby the sheet reference is presevred in the conditional formatting formula?

Thanks in advance for your kind attention.

Bharath

I figured this out. I can use the sumproduct& indirect function.
Thanks

Hi all,

Unfortunately, the sumproduct solution didn't work out, so I am back to sqare one. The formula that I used was-

SUMPRODUCT(COUNTIF(INDIRECT("'"&'Sheet names'!$E$1:$E$6&"'!B9"),"*"&"X"&"*"))>3

Where 'Sheet names'!$E$1:$E$6 is the range containing my sheet names. I need this formula to apply in cells B9:H19.

Any help would be much appreciated.

Thanks

Bharath

I will try this today. Thanks.

Originally Posted by bebo021999

How about:

Please Login or Register  to view this content.

Unfortunately this solution didn't work...

Originally Posted by bebo021999

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi,

Thanks.

Please see the attached mock file that illustrates my dilemma.

I would like a particular cell in the yellow highlighted region to turn red if the sum of the same cell in the six sheets with the letter "X" exceeds 3. This is the conditional formatting formula that I have entered in the first sheet and it performs well.
Problem is that once I copy paste the format to the other sheets, the formula gets altered and the conditional formatting doesn't work as desired on the remaining five sheets.
The only way around this currently for me is to individually paste the formula in each of the worksheets.

The file that I am working on has 72 sheets (six/ month). Each sheet has 5 formula based conditional rules. So I am looking at ways to avoid having to input 72x 5 (360) formulae manually!

Thanks again,

Bharath

Hi all,

I found the solution at last. This situation requires an incremental cell reference within the indirect function. The following formula worked for me-
=SUMPRODUCT(COUNTIF(INDIRECT(Calc!$B$1:$B$6&"!"&CELL("address",D4)),"*"&"X"&"*"))>3.

Thanks for all the folks who worked on this. Hope this solution would be useful for some of you.

Bharath