Need a column formula to pull balance from last row

BSReid24

I have looked at multiple questions and threads on the community and not found an answer that works. I am trying to create a column formula that pulls the [Balance] from the previous row into the current row entry. I was getting a #circular reference error, I tried IFERROR and some recommendations on other posts, but nothing I found worked.

Is there a formula that would allow me to pull the [Ending Balance] from the previous row into the [Starting Balance] on the current row, and not cause an error?

Matt Johnson

Hi @BSReid24

Have you tried not making it a column formula? For what you are describing, you could do it like this.

=[Ending Balance]1 goes into the highlighted cell below and then copied down. No formula in the top cell above that.

I hope that helps. -Matt

BSReid24

@Matt Johnson yes I had that as an option and working perfectly. My goal was to automate as much as possible to prevent team members from having to copy formulas if possible, to limit the chances of messing something up on the sheet.

Matt Johnson

@BSReid24 there's another way it could work by using a sheet Sheet Summary field on the right.

First put a "Initial Balance" field in the Sheet Summary and enter the initial balance.

Then put this column formula in the Starting Balance column: =[Initial Balance]# - Amount@row

You wont see the initial Balance on the grid part of the sheet but this should get you closer to what you want.

Matt

BSReid24

@Matt Johnson yes I have a formula that calculates the balance for each row and it works. So that formula would only duplicate what I have, if I am reading and testing it right.

I tried multiple uses of IFERROR and IF to reference the true beginning balance if on ROW 1 and if not then ROW-1. It made logical sense but I could not get it to produce without throwing an error, despite using IFERROR.

BSReid24

I think I found a solution: reference link below. This seems to be working.

https://community.smartsheet.com/discussion/112961/general-income-and-expense-sheet-referencing-cell-above-and-using-column-formula