I'm working on a large dataset in Excel (around 3300 rows). Each row uses formulas to derive values from a combination of worksheets together. All is well until someone tries to sort or filter the data. At that point the formulas stop working. Specifically the row numbers on the formulas become incorrect. Rows are moved according to the sort but the row numbers in the formulas end up wrong. For example if I check row 2 after a sort many of the formulas will reference an entirely different row. It doesn't even seem to be consistent with which references it changes- some references in the formulas are updated to match the new row after the sort but others are not.
Some of the formulas are pretty complicated and I'm working on a toaster of a laptop. It's excel 2010 running on Windows 10.
A work around seems to be to paste the formulas as values but this is not ideal as sheet is getting sorted to check the formulas are valid.
Any ideas?
Check your formulas for this: If you formula is in sheet1 and your formula is like =COUNTIF(Sheet2!A:A,Sheet1!A4) -> Change it to =COUNTIF(Sheet2!A:A,A4)
Delete the „Sheet1“ reference. Try to sort again and be happy ;)