Auto-Highlight Rows & Columns that Include a Specific Value

[GirvanShirman]

Hello,

First time poster here and would appreciate some help. I'm creating a strategy deployment matrix (Hoshin Kanri X-matrix) in Excel at present and I'm trying to introduce conditional formatting. I have attached an image below with the different sections of the sheet. Basically I want to use it to see how individuals in the department are involved in different projects, technologies and the tasks they are doing to support those. For each item that an individual is involved in, their initials will go into the cell. When I change the display goals initials (6), I would like all associated rows and columns relating to those initials to be highlighted for an easy way to see what individuals are involved in. I am unable to attach the Excel spreadsheet for some reason. The image I have below is a very simplified version of the matrix. The end product will have all of the cells populated with people's initials. Below is a legend of what the numbers mean on the image:

1: The different individuals in the department. If their initials are in the rows above their bolded initials, they have tasks in the How and By How Much sections for that project.
2: This will be a list of measurements relating to progress on the How tasks. If someone is responsible for a measurement or item, their initials will be above this list.
3: This will be a list of tasks that need to be done to help in achieving the deliverables.
4. This will be a list of the deliverables. The rows above this are coupled to the How section.
5. The individual technologies we will be working on and the deliverables associated with each are to the left.
6. The list of team members in the department. Each has their initials in a list under the "Display goals for" cell. CH is currently selected as the person whose involvement I want to visualise. This is the cell I want to reference for the row and column highlights. If the initials in this cell change, the highlight should change to the new persons involvement.

Capture.PNG

Please let me know if I need to provide anything else. I'll try and get the file attached. Thanks in advance.

[6StringJazzer]

It's a little unclear just what you want to highlight. It would have been helpful to show manual highlighting as an example. I am guessing you just want to highlight A3:P26.

The initials are in AA28 (this is a merged cell and we strongly discourage using merged cells).

You will need two rules, one to highlight rows and one to highlight columns.

Select A3:P26
In Conditional formatting, use "Use a formula" and use this formula for the columns:

Please Login or Register  to view this content.

Use this for the rows:

Please Login or Register  to view this content.

If this is not clear or doesn't do what you want then please attach the file. To attach a file, see the yellow banner at the top of the page.

[GirvanShirman]

Thanks for the quick reply. I've attached the sample worksheet now and manually highlighted the rows and columns forthe functionality I would like. I have highlighted everything that the CH individual will be involved in.

I'll give those formulae a go now!

[GirvanShirman]

Just following on from this I've encountered another issue and not sure if it's possible to do in Excel.

I have been able to get the suggested formulas working on my sheet so thanks for that. However I am trying to apply the conditional formatting (highlighting) to a selection of cells each consisting of 1 row but 3 columns wide merged together. Only when I put my value in the left most cell above the merged selection does the conditional formatting work. The centre and right cells have no effect on the formatting. Is there any way to enable the formatting across columns in the selection so that if I put an entry in the centre or right cells that it highlights the column? Thanks.

[6StringJazzer]

The conditional formatting rule in your attachment is not quite what I recommended. I have corrected your file. I am not sure how you got it working since you did not attach a working version.

As near as I can tell you only want to color down to row 9. Otherwise row 10 would always be colored, and 25-26 have no values in them that would trigger coloring.

As to your last question, I don't see that in your file (1 row but 3 columns wide merged together). "Only when I put my value in the left most cell above the merged selection does the conditional formatting work. The centre and right cells have no effect on the formatting." This happens because the merged cells take the formatting rule for the upper-left cell in the merged group. Excel has no way of knowing that you would also like to look at other cells. If you want the center and right cells to also affect formatting, you need to explicitly include them in your rule (or possibly have three rules). I can't help there without knowing the specifics.

Oh, and by the way, merging cells almost always turns out badly. Nearly every expert on this forum recommends against it.

[GirvanShirman]

Thanks for the response. I have attached my latest version of the table with the conditional formatting as I currently have it.

If you set the "Display goals for" drop down (cell BB18) to JM you will see that the rows and columns for JM highlight except for the box in row 10 that contains L3. However if you change the "Display goals for" drop down to GS the L3 box will highlight as the X is in the left most cell of the merged group. This is one issue I'm trying to fix but if possible I'd like to keep the merged cells as I need to have 3 different cell options for each of the L1, L2 etc groups. That's one issue I have.

My matrix works so that when a particular person is selected, the projects and goals associated with them should highlight. I'd also like to get this highlighting working in the other direction. So that whenever a particular technology is chosen from the "display team for" drop down in BD18, it will find that entry in the Technology section, see what column and row the X is entered and then highlight all columns and rows where people have their initials the whole back back to where the peoples names are entered on the right hand side. I have just started trialing formulae to get the rows highlighted (orange highlight).

[6StringJazzer]

The merged cells, for example, V10:X10 ("L3"), obey the CF rules for V10. If you want it to obey the CF rules for V10, W10, and X10, you will have to write two additional conditional formatting rules to do that. I still recommend you eliminate the merged cells. See this example, where all I have done is eliminate merged cells; no changes to conditional formatting.

[GirvanShirman]

The merged cells, for example, V10:X10 ("L3"), obey the CF rules for V10. If you want it to obey the CF rules for V10, W10, and X10, you will have to write two additional conditional formatting rules to do that. I still recommend you eliminate the merged cells. See this example, where all I have done is eliminate merged cells; no changes to conditional formatting.

Thanks very much for this. I'll try and not use merged cells in the future. It looks great now.

Is there a way to create conditional formatting for the matrix that is dependent on the "X" in the rows to the left of the Technology 1, Technology 2, Technology 3?

My overall goal is to be able to select a person and highlight what they are involved in (which you have helped greatly with so thanks for that) but also be able to select a technology and then wherever there is an "X" in rows 11, 12, 13 that corresponds to the selected technology, the columns above this will highlight then also highlight any rows in rows 3-9 that have anyone's initials in them. The highlighting then continues to the right where it matches the initials above the full names and highlights the entire name columns of anyone involved in working with that particular technology. So in the end, by filtering by a technology you should be able to highlight every individual tied to that technology and have better visbility on their involvement.

I will either be using the technology filter or person filter singularly. They won't be used at the same time.

[JeteMc]

Building on what 6StringJazzer has already done this proposal adds two helper ranges which may be moved and/or hidden for aesthetic purposes.
The first range, A23:AD25, is populated using: =AND($AE11=$BD$18,A11="X")
The second range is populated using: =IF(A23,INDEX(A$3:A$9,MATCH("?*",A$3:A$9,0)),"")
The conditional formatting formula that highlights cells A11:AJ13 is: =AND(COUNTIFS($A11:$AD13,"X")>0,$AE11=$BD$18)
Note that BD20 is another helper, which may be moved and/or hidden, and is populated using: =TEXTJOIN("",1,A27:AD29)
6StringJazzer's conditional formatting formulas are then modified to include cell $BD$20, as post #8 states that the technology filter and the person filter will be used singularly.
Let us know if you have any questions.

[GirvanShirman]

Building on what 6StringJazzer has already done this proposal adds two helper ranges which may be moved and/or hidden for aesthetic purposes.
The first range, A23:AD25, is populated using: =AND($AE11=$BD$18,A11="X")
The second range is populated using: =IF(A23,INDEX(A$3:A$9,MATCH("?*",A$3:A$9,0)),"")
The conditional formatting formula that highlights cells A11:AJ13 is: =AND(COUNTIFS($A11:$AD13,"X")>0,$AE11=$BD$18)
Note that BD20 is another helper, which may be moved and/or hidden, and is populated using: =TEXTJOIN("",1,A27:AD29)
6StringJazzer's conditional formatting formulas are then modified to include cell $BD$20, as post #8 states that the technology filter and the person filter will be used singularly.
Let us know if you have any questions.

Hello,

Thank you for the input. Apologies in advance for my Excel ignorance but I have a few questions about your additions:

- I see now that when the Technology filter is used, the row of interest from A11:AJ13 is highlighted. What would the formula be to then highlight the associated vertical column corresponding to where the "X" is? .i.e When Technology 3 is selected and tow 13 highlighted, column W is highlighted also and then another formula will search the block of cells A3:AD9 for cells that have entries corresponding to the already highlighted column and then highlight the rows corresponding to those cells.

- Not sure if I'm doing something wrong but do the helper cells you added return the true/false and corresponding engineer in the column of interest? Does what you already included have any functionality for highlighting that column instead of just outputting the engineers initials? If possible I'd like to keep it as visual as possible with highlighted rows and columns.

Would you recommend any resource for learning about basic conditional formatting and how it varies when you want to apply it to a row or column? I'm trying to warp my head around the different usage of the $ to alter the variable row/column. Thanks again.

[JeteMc]

I only added one new conditional formatting formula, for rows 11:13
The highlighting of the columns and rows 3:9 is still controlled by 6StringJazzer's formulas, I just added a bit so that if BD20 has a value, and BB18 is blank, then the value in BD20 is used in the COUNTIFS function.
As to the helpers, which are needed for my method to work, they could be moved as modeled to the last 7 rows of the spreadsheet, or the fill removed and the font set to white.
As to cell BD20 it could be hidden by setting the font to white, as modeled, or it could be moved to an out of site location.
The use of the $ has to do with references (absolute, mixed and relative). The tutorial linked below may help.
https://www.ablebits.com/office-addi...al-formatting/
Let us know if you have any questions.