CODEWITHSUNDEEP
excelsortingrowformula
Evis03
Evis03

Reputation: 17

Excel Sorting and filtering breaking formulas

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?

Upvotes: 0

Views: 12433

Answers (2)

Derek Koop
Derek Koop

Reputation: 1

If your are sorting on one sheet, but reference the cells on other sheets - the references on other sheets WILL NOT update. I use a unique ID column (in my case Transaction #) and VLOOKUP and/or FILTER to have formulas on other sheets update when sorting.

Example: In my case, I created a Transaction ID on the current sheet in column A. And summed all values in column B on sheet 2 that equaled the corresponding Transaction ID on Sheet2 row A.

For row 3 on sheet 1:

=SUM(FILTER(Sheet2!$B$2:$B$100,A3=Sheet2!$A$2:$A$100))

Sheet2

A    B
001 54
003 34
002 20
001 9
004 62

If A3=1, the result would be 63

If A3=3, the result would be 34

Upvotes: 0

Chris
Chris

Reputation: 943

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 ;)

Upvotes: 4

Related Questions