How to get the balance amount updated automatically?

LearnerExcel · #1 02-21-2025, 05:09 PM

Hi,

I need help updating the last column (Balance) automatically when I finish filling in the "Payments" column.

Thanks in advance for any help.

Logit · #2 02-21-2025, 05:38 PM

Paste the following in F3 :

Code:

=SUM(F2+D3)-E3

Then drag it down column F as far as needed.

ArviLaanemets · #3 02-22-2025, 11:46 PM

The is a problem with date! Is it the date when the invoice was get (probably it i this one), or the date of payment. When it is invoice date, it is possible, that this invoice was payed before some other invoices with earlier invoice date - making all this balance calculation wrong.

But when in column S.NO. are not formulas, but values, I'd prefer for Balance calculations the formula (I use the syntax used when the table is Defined Table)

Code:

=SUMIFS([Receipts],[[S.NO.]],"<=" & [@[S.NO.]])-SUMIFS([@Payments],[[S.NO.]],"<=" & [@[S.NO.]])

(This formula doesn't depend on order of rows in table! So you can sort the table in any way you want, and the calculated balances remain unchanged!)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find % change from last month automatically updated	GraemeSS	Excel	7	04-03-2020 02:50 AM
LINK field path will be updated automatically	bynkook	Word	1	02-28-2020 02:54 AM
Keep balance column empty if no amount entered	baes10	Excel	3	08-14-2018 06:31 AM
How to know the outstanding balance amount of a Loan?	PRADEEPB270	Excel	1	01-07-2012 04:45 AM
How can references be updated automatically?	ezthelm	Word	2	05-30-2011 03:45 AM