Automatically add formula to new row & apply @row to look at row above

AbbyT

I have my cashflow forecast on Smartsheet, and i have a formula which looks at the balance on the row above to apply the balance for the current row as follows =BALANCE1350 + [in's]1351 - [out's]@row.

When i shift the row up or down within the sheet, it keeps looking the the balance original balance on the row that was above it, not the new row above. (see screenshot example)

Is there any way i can get it to automatically look at the balance on the new row above?

i know how to use @row for a formula to look at something on the same row, but not the row above.

Also is there a way to get the formula to be automatically added to new rows inserted in-between existing rows (not at the bottom or top of the sheet)?

AbbyT

Cashflow formulas.docx

Sorry see example attached now

Alejandra

Hi @AbbyT,

Smartsheet will automatically update cell references when a formula is moved or copied —unless you've created an absolute reference. Since the row you're moving only contains references to the columns named "in's" and "out's", only those cell references are changing.

Drag filling the formula down the column will give similar results to what you're looking for, however, drag filling a formula is different from moving a row. When you have a moment, submit a Product Enhancement Request to let our Product team know that you'd like to have this functionality in Smartsheet. 

Also, formula autofill will automatically populate a formula when you type in a newly inserted or blank row that is directly between two others that contain the same formula in adjacent cells.

AbbyT

Thanks for your reply Alejandra

Yes i understand you can drag the formula down, but this takes extra time, and is easily forgotten

I have submitted an product enhancement request.

Im suprised there is no formula for this already