Sorting data with formulas

[Herakles]

Good evening

I have a woksheet with a COUNTIFS formula using two criteria on another worksheet in the same workbook.

If I sort the worksheet the formula makes reference to a cell in the right column but in a different (incorrect) row.

I assumed that it would keep the row reference correct when rows are sorted.

Can anybody offer any advice.

Thanks.

 

[Herakles]

I seem to have fixed it by removing the reference to the sheet in which the formula is.

E.g. =COUNTIF(Sheet2!A:A,Sheet1!A4) -> Change it to =COUNTIF(Sheet2!A:A,A4)

I found this fix onStackOverflow.

How do I close this post?

Thanks

 

[Fluff]

How do I close this post?

Glad you sorted it, all you need to do is mark post#2 as the solution. (Click on the tick to the right of the post)

It's always best to never refer to the sheet the formula is in as it can cause problems. As you have discovered.

 

[Herakles]

Glad you sorted it, all you need to do is mark post#2 as the solution. (Click on the tick to the right of the post)

It's always best to never refer to the sheet the formula is in as it can cause problems. As you have discovered.

Thanks Fluff.

Excel is a peculiar beast with many odd ways of doing things.

I often wonder how design and development decisions are made at Microsoft.

I've just set up a dop-down menu for someone.
Why does it only contain a maximum of 8 options and why does it not default to the font and font size in the cell.

A rhetorical question of course unless you know the answer.

Take care and stay safe.

 

[jasonb75]

Excel is a peculiar beast with many odd ways of doing things.

I often wonder how design and development decisions are made at Microsoft.

With regard to the sorting problem that you encountered, it is most likely that it was done to prevent errors, with sheet name follow the cell when the data is resorted, without sheet name don't follow.

Consider that your formula, =COUNTIF(Sheet2!A:A,Sheet1!A4) was in sheet 3 instead of sheet 1. If you sorted sheet 1 and the formula retained the original references then it would most likely be looking in the wrong place.

The example scenario that I've used would be more realistic with other types of formula than the one that you have used. I would imagine that a variety of models are used for how and why decisions, with the final choice being based on what works best with the most test cases.

Given that almost every user has a different scenario to work with, the chances of a perfect solution on such a feature are going to be quite slim.