Forum Discussion
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 looks like this.
=SUMPRODUCT(COUNTIF(INDIRECT("'"&I2:I61&"'!C7"), E12))
Beside the column with the values is column "I" which is used as the list of sheet names for the formula. (I2:I61). If the value returned is say "12", is there a way that once clicked on, the 12 sheet names in which that model number were found would automatically highlight in column "I"?
I appreciate any help on this one, I'm very new to Excel and this is a bit of a learning curve!
5 Replies
=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.
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?
You are welcome. In your screenshot the formula refers to cell F12 instead of E12. However the formula is supposed to do what i've suggested in my last reply. I forgot to mention that it's an alternative solution.
In the attached file you can select a cell in range F12:F18 and then cells in range I2:I61 are highlighted according to the conditions. This is only possible with VBA and should return the result you are looking for. You can select cells in range F12:F18 with the up and down and left and right keys of the keyboard as well.
- Hello dborg1330
Huh???
You want to click on a cell and have what happen? Please explain better...
Are you familiar with Visual Basic for Applications (VBA) the automation programming language in Microsoft Excel? If yes are you familiar with the worksheet events that you can trap and run code?
OK I am getting too old... I need new glasses, or maybe a whole new brain... I'm not sure.
Georgie.Hello, unfortunately I am not familiar with VBA, I have just recently started using Excel for tracking deficiencies in new home construction and my experience with coding is minimal. I'll try to explain better what it is I am trying to do.
I have 60 identical sheets with Suite Selections in them, this includes all upgrades and notes for finishing a suite. On the 61st sheet I am tracking totals of things for ordering purposes so the set up is roughly like this...
Column "A" is a list of appliance model numbers.
Column "B" is totals for each model pulled from the 60 sheets using
=SUMPRODUCT(COUNTIF(INDIRECT("'"&I2:I61&"'!C7"), E12))
Column "C" is the list of the 60 sheet names (suites) from which the above formula pulls totals.
(101, 102, 103 etc.)
When I click on a cell in Column "B" I would like the associated cells in Column "C" to highlight indicating where those appliance models are to be installed.
