Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Locking Formula ???
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.
Comments
-
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
