How can I force Relative Cell References into a Conditional Formatting Forumual in Excel

January 30, 2012 3:50 PM

In Excel I have 2 columns with dates in them. D1:D20 Start Date G1:G20 End Date. I want to highlight the cell in the range G1:G20 if the date is more than 14 days form the date in D1:D20. Obvious answer is conditional formatting, but I can't seem to create a formula with relative cell references.

I tried using conditional formatting, but it seems to be forcing absolute cell references (even though it doesn't look like it should) so that every cell in G1:G20 is comparing itself to D1. How can I set this up so that G3 compares itself to D3?

posted by willnot to computers & internet (6 answers total)

Does it have to be highlighted in G, or can you create a new column E that has the range difference between D and G and then use conditional formatting on that? That would be much more straightforward.
posted by brainmouse at 3:51 PM on January 30 [1 favorite]

You should be able to delete the $s on the conditional formatting. Which version of Excel are you using?
posted by jeather at 3:52 PM on January 30 [1 favorite]

Did you see this question I asked? It was about conditional formatting, and some answers were very helpful.
posted by jph at 4:04 PM on January 30

You have to use the INDIRECT() function within the conditional formatting settings.

Like so:

=(INDIRECT("G"&ROW())-INDIRECT("D"&ROW()))>=15
posted by mullacc at 4:06 PM on January 30 [1 favorite]

I don't why I used >=15 instead of >14. Both work.
posted by mullacc at 4:09 PM on January 30

Thanks mullacc - that was the magic I was looking for.
posted by willnot at 4:17 PM on January 30