20

I have a google sheet where I want the range A:D to format a certain color if the contents of A2 contain the word Order (case insensitive). Right now I have the following:

  • Conditional Format applies to range - A:D
  • Format cells if... - "Custom formula is"
  • Custom Formula - =SEARCH("Order",$A$2)
  • The chosen background color for the formatting style

So the trouble is the formula =SEARCH("Order",$A$2) searches exactly cell A2 for every row. I want every row to be relative so that row 3 searches A3, row 4 searches A4 etc etc. How should the custom formula be written so that it's relative instead of exact?

I can't get this to work for some reason, and I have hundreds of rows so I can't just add the conditional formatting individually. I thought that the $'s were absolute references which makes complete sense but it won't let me save the formula as =SEARCH("Order",A2) - it tells me it's invalid and won't save that way.

EDIT: So I can save the =SEARCH("Order",A2) formula if I change the range from A2:D2 - but now the problem is that ONLY A2 gets highlighted when I want the whole row to highlight - the A2 relative reference applied to B2, C2, D2 when I want all of those cells to only reference A2. How do I get the ROWS to be relative and not the COLUMNS?

Help is appreciated!

ifconfig
  • 6,242
  • 7
  • 41
  • 65
user3499449
  • 263
  • 1
  • 2
  • 7

3 Answers3

40
"A"&ROW()

Returns A8 if you're on row 8 (regardless of column), A9 if you're on row 9, &c.

You can use this along with INDIRECT(), which takes the value of a cell given by a string.

INDIRECT("A"&ROW())

Returns the value in A8 if you're on row 8, the value in A9 if you're on row 9, &c.

You can also use R1C1 notation to generalize this to columns as well:

INDIRECT( "R" & ROW() & "C" & COLUMN() )
Zaz
  • 46,476
  • 14
  • 84
  • 101
  • 2
    Thank you. I've been looking everywhere for this information! – Sky Feb 09 '18 at 21:26
  • I find the way that conditional formatting works with custom formulas is quite strange. I was able to use both of the following equations to get the same result: [1] =INDIRECT("E"&ROW()) – greg7gkb Mar 31 '22 at 21:12
11

$ is to "anchor" to that row/col. If you want the column to stay static, use $A2 and the row can adjust freely.

panhandel
  • 674
  • 13
  • 23
  • 1
    Haha, thanks, I really should have given it 10 more minutes before posting, I just hit a wall and then made a breakthrough right before you posted the answer. Thanks! – user3499449 May 22 '15 at 16:49
  • 9
    Yes, I understand $ is an absolute reference. What I don't understand is, how do I indicate a _relative_ cell, or which cell is it relative _to_? For example, if I apply the conditional format to `(G:L)` (columns G to L), and want to format the cells if, say, column H on the previous row is blank, what row number to I put in the formula? It would be easy if I wanted to apply it to just one row—for row 5, I'd just indicate the previous row with `ISBLANK($H4)`. But I'm applying it to all the rows, and I don't know how to specify 'the previous row'. I suppose I'm asking, which row is the context? – Michael Scheper Nov 27 '15 at 00:58
6

Phew, finally figured it out - I can change the absolute references to be row/column specific, the trouble was that $A$2 was referencing and exact cell and A2 was relative to both row and column - I wanted to use $A2 so that only row is relative, so the answer to my question is to use this formula:

=SEARCH("Order",$A2)

user3499449
  • 263
  • 1
  • 2
  • 7