Electra_Pink
New Member
- Joined
- Sep 8, 2005
- Messages
- 6
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
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