Sign in to join the conversation:
Hi Team,
I find my formulas change when i filter etc.
e.g. =SUM([Value Of Jobs]2:[Value Of Jobs]85)
=SUM([Value Of Jobs]18:[Value Of Jobs]85)
My top row which i use to calculate columns keeps changing. How to I fix or lock formulas please.
Hi,
Filtering isn't designed to change cell references in formulas, there has to be a different behavior causing that—I'm guessing that someone is moving the row containing the formula after applying a filter, which will change the cell references.
If you need to move the row around but want the cell references to be static, add $ to the references:
=SUM($[Value Of Jobs]$2:$[Value Of Jobs]$85)
More on this in the help article on creating formulas: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet
Otherwise, if you don't want anyone to move the row around AT ALL, then you'll want to lock the row containing the formula: https://help.smartsheet.com/articles/522077-locking-unlocking-columns-and-rows
Note on locking the row: you'll need to give everyone else Editor access to prevent them from moving the locked row around. If you're the sheet owner, you'll still be able to move the row.
Thanks Shaine
If it moves down the page when filtering (Asending) it changes. When i (Desending) so it goes back, it goes back to the origanal lock setting which is hughly helpful. Thanks Again
