I'm trying to conditionally format a group of cells against one of several headers at the top of the sheet. Each header row contains "default" values for a certain type of data, and there are several header rows. Rows will be sorted by data type to hide the other header rows later. The formatting that I need to do is to find cells that exactly match the respective cell in the header row (the designated default value for that parameter).
For example, here the "data type" is Model A or Model B, with the default parameter for color being Red for Model A and Blue for Model B. I want to highlight any color cell that contains the same parameter as the default of that row's data type. In this example, I'd want to find that row 3, 5, and 6 have color parameters that match the default colors defined in the first 2 rows and therefore highlight B3, B5, and B6.
The conditional formatting formula I'm using is:
=INDIRECT("A"&MATCH($A1,$A:$A,0))
Applied to $A$1:$B$9. I realize this will also highlight all my default values in the header rows, but for simplicity's sake I'm fine with that for now.
The first thing I need to do is find the row number of the first instance of any row's data type, which I do with the MATCH function. I then build an indirect reference to compare the current cell to the "default value" for the data type, which is in the same column. I'm wondering if the relative reference (=INDIRECT("A"&etc.) is not recognized because it's really just a string within the indirect function. When I compare the formula results in another column, I can see that I'm finding what I need.
Are the relative changes to the formula applied after the indirect function is evaluated? Or am I just missing something fundamental to conditional formatting rules?
VLOOKUPorINDEX/MATCH. Doesn't sound like you needINDIRECT. Or maybe I misunderstand your question, but I think this would be easier to understand with a screenshot of sample data and the expected result of conditional formatting. – BigBen