sheet summary error

Sofia Fernandez

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?

Genevieve P.

Hi @Sofia Fernandez

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 drag-fill the formula up and down without the reference auto-changing 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

Paul Newcome

What are your formulas?

Sofia Fernandez

=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

Paul Newcome

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

Sofia Fernandez

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.

Paul Newcome

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.

Sofia Fernandez

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?

Paul Newcome

@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?

Genevieve P.

Hi @Sofia Fernandez

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 drag-fill the formula up and down without the reference auto-changing 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