Getting cell value of composed address

  • Thread starter Thread starter dalton
  • Start date Start date
D

dalton

I have a grid of data where I am trying to apply conditional
formatting. The logic is that, for every cell in a column, if it's
value matches what is in row 3 of that column then I want to apply the
conditional formatting. So I have the following formula:

=EXACT(OFFSET($E$3,ROW()-3,0),$E$3)

and it works. The problem is that I have to apply this to every row,
and each time the only modification I have to make is to alter the
hardcoded column letter. So I came up with the following:

=EXACT(OFFSET($&IF(COLUMN()<27,CHAR(COLUMN()+64),"A"&CHAR(COLUMN()+38))
&$3,ROW()-3,0),$&IF(COLUMN()<27,CHAR(COLUMN()+64),"A"&CHAR(COLUMN()
+38))&$3)

which is an attempt to generalize the column reference. It does not
work because Excel appears to require addresses to be hardcoded.

Does anyone know how to solve this problem, or more generally put, how
to get the cell value of a composed (not hardcoded) address?
 
One way:

Select your cells, with, say, E4 the active cell. Choose
Format/Conditional Formatting:

CF1: Formula is =EXACT(E4,E$3)
Format1: <your format here>


XL will automatically adjust the cell addresses.
 
Select your cells, with, say, E4 the active cell. Choose
Format/Conditional Formatting:

CF1:     Formula is   =EXACT(E4,E$3)
Format1: <your format here>

XL will automatically adjust the cell addresses.
Click to expand...

I had thought of that but didn't think it would be reliable. Turns
out it does work just as desired. Thanks for the quick response.

Dalton
 
Back
Top