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 agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.