Sorting issue when formula includes sheet name

[Electra_Pink]

I know the answer to my problem. My question is why is it happening?

If you include the sheet name of the current sheet in a formula and then sort the data, the formulas do not sort.

For example:
1 1
2 2
3 3

Where the second column references the first column with the formula: =Sheet1!A1 copied to all following rows.

Then, if you sort the entire range by Column A, descending, you get:

3 1
2 2
1 3

Where the formula in B1 now references A3 and B3 references A1

However, if you change the formula to exclude the sheet name and reference only the cell, =A1 and copy that to the other rows, then sort the whole range by column A, descending, it sorts correctly.

3 3
2 2
1 1

WHY?

It's acting like an absolute reference here but does not act like an absolute reference when you copy it from cell B1 to B2 and B3. I know the simple answer is, ”don’t include the current sheet name in your formulas”. But the reason this is a problem is that when you are entering a formula (e.g., vlookup, sumif) that includes references to another sheet it then automatically includes the sheet name for all subsequent references, including those on the current sheet. Now that I KNOW this, I’ll try to be more careful. But I’m wondering if there’s a more solid workaround beside relying on my human abilities.

Thanks so much in advance and thanks too for all the past help. I love this board! - Deborah