REF# error on summing ranges

[Guest]

Currently, I sum up resource invoices by the following statement:
=IF($B14<>$B13,IF($AK13="yes",SUMIF(B$2:$B13,$B13,$BK$2:BK13),$BL13),"")

B = resource, AK = invoice is for this year, BK =invoice $, BL = default
value if no invoice $.

This formula works great - EXCEPT at year end cleanup when I need to remove
all the old rows no longer required for reporting. For each line I remove,
the line about gets a REF# error.

How can I get around this?

 

[Tester]

Why don't you make a template of the sheet so that you can use a new one
each year?
Otherwise, I don't know how many lines you input but what about Hiding the
rows you don't want to see?
HTH

 

[Guest]

We don't start with a clean slate at the beginning of each year, but need to
only remove the rows for the resources that are no longer with the company.
I'd prefer not to hide the rows as the file is quite large. The spreadsheet
has lots of complex calcs and we have an admin that manages it - and I was
hoping I didn't have to draft up a process for her to copy specific
cells/formulas throughout the spreadsheet.