Every time I add a row to the top of this sheet, the formula changes even though I'm using '$'
I use Smartsheet often, but I'm beginning to use the formulas more and more. I can't resolve this problem. Every week, a new row is added to the top of the sheet. The "Should Have" row, needs to calculate the "MAX" value of the top 4 rows (and since a row is added each week, the top 4 rows change continuously).
Whenever I add a row, the formula updates like this:
Any help would be appreciated.
Thank you!
Answers

Hi @Daysnoc1 — if you want to get the top row each time, you may need to set things up a little differently. As you've noted, Smartsheet will 'helpfully' update your formula if a new row is added using the $ convention. In general, I avoid putting single formulas in the same column as the information you are summarizing. I would put a single formula like this in the Sheet Summary tab (or in a different column). If you do this, then you can easily write the formula to collect the entire column, without having to specify rows. You use the [ColumnName]:[ColumnName] convention. That formula would look like this:
=MAX([100]:[100])*3
This will always give you largest value for that entire column. However, if you tried to add this formula to the cell you are currently using, because it's in the same column you are referencing, you would get a Circular Reference error.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 218 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!