Linked deletion or linked formatting?

[mrckson]

I have a workbook with a data entry sheet, and then three other sheets displaying certain weekly statistics linked to the data entry sheet. There are times, though, when I need to delete a week's data, but that leaves me with a column of #REF , and it screws up the conditional formatting I've put in place. Is there some way to set it up so that I can delete a range of data from the entry sheet, and have the cells on the display sheet be automatically deleted as well, while having the conditional formatting be preserved? Or would it be possible to have the linked cells copy the formatting as well as the displayed value?

 

[Roderick_E]

how about making your formulas display something else if #REF happens, like a zero?

iferror(the formula,0)

that way if you delete things it doesn't return #REF

 

[mrckson]

Thanks, but the #REF will still be there, so the conditional formatting won't work properly.

 

[mrckson]

I meant, the #REF won't be there, but it's still an unwanted cell that's there, affecting the formatting.