Forum Discussion

dborg1330's avatar
dborg1330
Copper Contributor
Mar 19, 2024

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

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.

  • 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.

    • dborg1330's avatar
      dborg1330
      Copper Contributor

      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?

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        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)

Resources