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?

  • How are you copying the formatting? Have you tried changing the "Applies to" field for you conditional formatting rule? – Excellll Aug 19 '14 at 15:54
  • Applies to doesn't work because the cell references stay the same and are just duplicated. – Alex S Aug 20 '14 at 8:08
  • I'm guessing I'll need to change the formula so that when I copy and paste or use the format painter, the references update? – Alex S Aug 20 '14 at 9:29

Your Answer


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.