Forum Discussion
Highlighting sheet names if a specific value is found in that sheet.
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.
HansVogelaar 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?
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)
- 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?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.