I have a table in my Google Sheet where the rows are a value that goes from 1 to 12. The columns go from 1 to 10. Each cell is then a computed value based on a formula that the row and column are inputs for.

What I would like to do is have two dropdowns that together will trigger the cell at that intersection to have its background colored. One dropdown contains valid values for the rows. The other dropdown contains valid values for the columns. So if the row dropdown is 9, and the column dropdown is 8, I'd like the cell at that intersection in the chart to have its background changed to some specific color. How do I go about doing this?

CodePudding user response：

Using conditional formatting with the custom formula below will solve the issue:

=AND(INDIRECT(ADDRESS(1, COLUMN(), ,TRUE))=$C$16,INDIRECT(ADDRESS(ROW(), 1, ,TRUE))=$B$16)

Where:

• B16 is the cell for row dropdown
• C16 is the cell for column dropdown
• the 1 inside the address (before the COLUMN() parameter) equated to C16 is the row where the column values are placed (in my sample below, the first row which is why it is 1)
• the 1 inside the address (after the ROW() parameter) equated to B16 is the column where the row values are placed (in my sample below, the first column which is why it is 1)

Breaking down the formula:

• INDIRECT(ADDRESS(1, COLUMN(), ,TRUE))=$C$16
  • return TRUE if first row contains the value of C16
• INDIRECT(ADDRESS(ROW(), 1, ,TRUE))=$B$16
  • return TRUE if first column contains the value of B16
• If both are true (row and column values are found respectively), then return TRUE. This will now change the color where the row and column matching values intersect.

Note:

• If multiple values are found, all of their background colors are going to be updated. No matching values will result to no change.
• Representing sample data is the result of multiplying both rows and columns.