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:
A:D
"Custom formula is"
=SEARCH("Order",$A$2)
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!
$ is to "anchor" to that row/col. If you want the column to stay static, use $A2 and the row can adjust freely.