Share via

Absolute reference doesn’t work when sorting data?

Anonymous
2016-03-13T04:53:19+00:00

This I wonder as based on my experience, I’ve referenced to a data as an absolute reference and I sorted the data hoping that it would follow the one referenced absolutely but it did not.  Is there a solution for this?  (I’m not sure though if it’s because of the complexity of my worksheet that’s actually causing this).

So this is the original where I made an absolute reference to a data, i.e. S$127, value 1.33.

And after sorting the data, it would turn out that the absolute formula did not follow where the actual data is, but merely retained the absolute formula, i.e. S$127, so that the value changed to 18.26.

The data with the absolute reference formula:

And the data being referred to:

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-03-13T12:39:00+00:00

    Re:  sorting data

    Your pictures illustrating the problem don't...

    Sorting data containing formulas usually screws up the data.

    So, don't sort data containing formulas.

    The alternative is to copy/paste values to another part of the sheet and then sort.

    '---

    Jim Cone

    Portland, Oregon USA

    0 comments
  2. Anonymous
    2016-03-13T21:33:57+00:00

    Well, alright then.

    Well, I think when the formula references within a column, you would have a problem as this is the first time I referenced within a column.

    But I've been using INDEX/MATCH and doing data sorting and having no problems then.

    0 comments
  3. Anonymous
    2016-03-14T02:05:19+00:00

    Hey!  I thought of amending the formula of the one which do not follow the absolute reference, and amending it to INDEX/MATCH, and it worked!

    0 comments