Locking Formula ???

Chrissmartsheet

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. 

Shaine Greenwood

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.

Chrissmartsheet

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