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.

General Income and Expense Sheet - Referencing Cell above, and using Column Formula

Msman

Nov 13, 2023

Hi Everyone,

Basically trying to create a basic income and expense sheet, moving from Excel.

Where the first row, has a fixed value and the rows after will have a formula of balance from previous row - payments from current row + deposits from current row. The formula is dragged along the Excel sheet.

Excel has only 3 columns : Payments, Deposits and Balance

Now trying to automate and replicate this in Smartsheet, using Column formulas has been a nightmare. So far based on previous queries, I have set this sheet in this particular way to replicate the Excel set up but in an automated way and used the below formulas. It has been very challenging to do so, and getting an error in some form or the other. Open to ideas, and can change sheet structure too, with more or less columns.

Thanks for the help.

MS

Previous Row Reference Column (fx) = Row@row - 1

Row Column (fx) = MATCH(Auto@row, Auto:Auto, 0)

Payment + Deposit Column (fx) = (IF(Row@row = 1, {Total Funds Available}, IF(OR(Payment@row <> "", Deposit@row <> ""), SUMIFS(Payment@row:Payment@row, Row@row:Row@row, <=Row@row) - SUMIFS(Deposit@row:Deposit@row, Row@row:Row@row, <=Row@row), "")))

Previous Balance Column (fx)= IFERROR(INDEX(Balance:Balance, MATCH([Previous Row Reference]@row, Row:Row)), {Total Funds Available})

Balance Column (fx) = IF(Row@row = 1, {Total Funds Available}, [Payment + Deposit]@row + [Previous Balance]@row)

{Total Funds Available} is a cross referenced value from another sheet, which is inputed, this number can be a hard entry as well.

https://us.v-cdn.net/6031209/uploads/8B583MUDGQW2/image.png

---

View Post