Highlighting sheet names if a specific value is found in that sheet.

[dborg1330]

Hello,

I have a workbook with 40 identical sheets containing finishing details for suites within a condo.

The 41st is an overview of those sheets and is set up as follows.

Column "E" contains appliance model numbers

Column "I" contains the sheet names (101, 102, 103 etc.)

Column "F" contains the formula =SUMPRODUCT(COUNTIF(INDIRECT("'"&$I$2:$I$61&"'!$C$4"), E12)) which returns the amount of times the model number from column "F" appears in the sheets listed in column "I"

I am not sure if this is possible but I would like a formula for column "H", that when selected, highlights the names of the sheets in column "I" in which the model number from column "E" is found.  Basically, I want a quick and easy way for non-excel users in my office to get a list of sheet names in which a specific thing is found in a specific cell.

[#2]

@dborg1330 

Select H2.

On the Data tab of the ribbon, in the Data tools group, click Data Validation.

Select List from the Allow drop-down.

Click in the Source box and point to the list of unique appliance model numbers in column E.

Click OK.

Next, select the list of sheet names in I2:I61.

I2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.
Enter the formula

=COUNTIF(INDIRECT("'"&I2&"'!C4"),$H$2)

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

When you select an appliance number from the drop-down in cell H2, the cells in I2:I61 corresponding to a sheet that contains the appliance number will be highlighted.

[#3]

@Hans Vogelaar Thank you so much, Hans! That worked in my appliances page perfectly.

Because it worked I got the idea that the 40th page could just be another version of the first sheets because all the data validation drop downs are already in place. I created rows at the top for sheet names and froze those rows so I could scroll the sheet and select any ONE item from any ONE drop down to get the sheet names (suite numbers) to highlight in my frozen header. I tried it for the first (C4) cell but it didn't work this time. Is this because the rows are frozen or because the sheets names are not in a single column? Is there something else I'm missing?

[#4]

@dborg1330 

You should select B1:F8 when you create the rule, and B1 should be the active cell in the selection.

Refer to B1 instead of B2 in the formula:

=COUNTIF(INDIRECT("'"&B1&"'!C4"), $C$12)

[#5]

PERFECT! Thank you again, not sure why I had B2 in there and not B1.

I am going to build-out the sheet now for probably 80 drop downs. Do I need to create a separate condition for each drop down or is there a way to create one condition for all of them?

My only other question would be, if a future project has more suites, can I just copy and paste the conditioning to include the cells with the additional sheet names?

[#6]

@dborg1330 

You'll probably need a separate source for each drop-down list.

I think you'll be able to copy/paste the conditional formatting cells.

[#7]

Thank you so very much for all your help! Everything works perfectly!