Forum Discussion

dborg1330's avatar
dborg1330
Copper Contributor
Feb 05, 2024

Conditional Formatting - Highlighting sheet names linked to a value in a cell

  Not sure if this is even possible, but thought I'd ask. I have cells returning values representing how many times a MODEL # is found in specific cells across 60 sheets. The formula works and look...
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Feb 05, 2024

dborg1330 

=SUMPRODUCT(COUNTIF(INDIRECT("'"&I2&"'!C7"), $E$12))

In the attached file i've made an example for 12 sheets with this rule for conditional formatting.

=$I$2:$I$13

This is the range the conditional format applies to in the example.

dborg1330's avatar
dborg1330
Copper Contributor
Feb 08, 2024

OliverScheurich

Hello, Oliver

Thank you for your reply, but I think I have done something wrong here.

 

F12 contains the formula =SUMPRODUCT(COUNTIF(INDIRECT("'"&I2:I61&"'!C7"), E12)) which is returning that there is 1 instance of the MODEL# from E12 occurring in my 60 sheets, in this case it occurs in sheet "110". I'd like to be able to click on a cell and have "110" highlighted in column "I".  I tried inserting your formula into cell H12 to achieve this but there is something wrong with the formula. Have I put it in the wrong place or typed it wrong?