I am applying conditional formatting to cells on my spreadsheet, using the following formula in cell C6:

`=INDEX('All Time'!$F$2:$F$550,MATCH(INDEX('All Time'!**$A$7**:$A$550,MATCH("*",'All Time'!$A$7:$A$550,0)),'All Time'!$A$2:$A$550,0)-1)`

When I apply the formatting to cell C7, I want the formula to update to:

`=INDEX('All Time'!$F$2:$F$550,MATCH(INDEX('All Time'!**$A$8**:$A$550,MATCH("*",'All Time'!$A$7:$A$550,0)),'All Time'!$A$2:$A$550,0)-1)`

I had assumed that removing the $'s from the formula would mean the reference updates automatically, just as it would if I copied the formula within the spreadsheet but this doesn't seem to work.

Is there a way to make references automatically update when copying conditional formatting onto new cells?