Conditional Format Reading Multiple Sheets/Tabs

Question

Looking for help using "Conditional Format" across multiple tabs/sheets.

I was able to figure out how to get it to work for one worksheet/tab, but no others.

What I would like for it to do is highlight cell C2 from sheet 1, when an exact match for criteria is made on tab/Sheet 2. I need it to do the same on sheet 1 when sheet 3 information is entered. So on and so forth. If I have 20 sheets, I need the 1st sheet to pick up on the exact value entered in column C and Highlight that Number in Sheet 1.

Is there a way to do this?

Answer 1

Hi RazorTusk. I am an Excel user like you.

To do this you will need to do two things. Add a Named Range to each of your sheets and add a helper column somewhere (ideally on your Sheet 1 where you are applying the Conditional Formatting). The helper column is necessary because the formula that can examine all of the sheets at the same time is not compatible with the Conditional Formatting operation.

1. Start by adding the Named Ranges to each sheet two through twenty. Select the range of cells in Column C of Sheet 2 that you want to include, i.e. C2:C100 or however many rows you need included. Click in the Name Box at the upper left of the sheet and type the name for the Named Range. I just used "sheet2", etc.; then press Enter to confirm the name. Repeat this process for each of the other sheets so that the Column C range is named on each sheet.

2. Choose where you want to add the helper column. It can be off to the right on your Sheet 1 and can be hidden after adding the formulas. In Row 2 of that column enter this formula. I put it in E2 below. Then drag down.

=SUM(COUNTIF(INDIRECT({"sheet2","sheet3","sheet4","sheet5"}),C2))>0

NOTE: I only used Named Ranges through sheet 5 in the example. Enter all of your named ranges in the formula. Be sure to watch the punctuation of the quotes and commas.

You can now add the Conditional Formatting using the True/False results of the formula. Select all of the cells in Column C of Sheet 1 that you want formatted beginning with C2. Open Conditional Formatting>New Rule>Use a Formula. Enter this formula adjusting the column reference to match the location of your helper column. Set the formatting you want and click OK and OK.

=E2=TRUE

Any value in the range applied to the Conditional Formatting that is found in any of the Named Ranges in the helper formula will be formatted.