sheet summary error
The formulas in sheet summary change based on what column I sort by.. very unhelpful. (Even if I add $ to my formula....) Anyone else run into this bug?
Best Answer

Paul is correct! The reference is in regards to that specific cell, no matter where the cell is moved in the sheet. An absolute reference locks the formula so you can move and dragfill the formula up and down without the reference autochanging as you shift the placement of the formula... however if you move the row with the referenced cell to a different location, the formula's row number will shift to follow that specific piece of data, as Paul outlined.
That's a good question in regards to documentation. There is a Help Center article on creating references in formulas (see here), which identifies that the row numbers are to select a cell (versus locking to a row/placement in the sheet) and reference the data inside that cell.
However there is no description/documentation of what will happen to that row number when you Sort a sheet that I can see. I'll provide feedback on that article to see if we can clarify this; if you wouldn't mind also providing feedback (through the "Was this article helpful?" question on the very bottom right) I would appreciate it!
Thanks,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

What are your formulas?

=SUM($[Difference in Pay]$1:$[Difference in Pay]$117) * 0.87
= 18520.4034
and then when I sort that column.. or any other column.. I get a variety of different numbers..
here is one example when I sorted it A to Z
=SUM($[Difference in Pay]$37:$[Difference in Pay]$117) * 0.87
= 0

Is there a reason you are referencing specific row numbers instead of the entire column?

I understand that you can get the "correct answer" by referencing the Column:Column; but I don't see why logically the output of the formula should change when the order is rearranged.
This is a bit unintuitive.

The reason for this is that your formula is referencing the cell itself and not necessarily the location. If you reference [Column Name]1 and then move that cell to a different location within the sheet, the formula will automatically update to follow that cell.
I believe it is designed this way so that if you have multiple formulas referencing a specific cell and then later realize you want that data moved, you can just move the data and not have to worry about updating all of your formulas for the new location.

Okay, I did not know that. Also, I haven't read that nuance before: separating out location from actual cell. Do you have a reference for that?

@Sofia Fernandez I don't have a reference for that. I am only speaking to personal experience and my own thoughts on "why".
@Genevieve P Are you able to confirm/provide any kind of documentation on the subject?

Paul is correct! The reference is in regards to that specific cell, no matter where the cell is moved in the sheet. An absolute reference locks the formula so you can move and dragfill the formula up and down without the reference autochanging as you shift the placement of the formula... however if you move the row with the referenced cell to a different location, the formula's row number will shift to follow that specific piece of data, as Paul outlined.
That's a good question in regards to documentation. There is a Help Center article on creating references in formulas (see here), which identifies that the row numbers are to select a cell (versus locking to a row/placement in the sheet) and reference the data inside that cell.
However there is no description/documentation of what will happen to that row number when you Sort a sheet that I can see. I'll provide feedback on that article to see if we can clarify this; if you wouldn't mind also providing feedback (through the "Was this article helpful?" question on the very bottom right) I would appreciate it!
Thanks,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 361 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!