Report a debt credit balance according to dates

[Tacir]

Hello friends,

Can I request your help about the table I added ?

I specified the necessary explanations in the document.

Yours truly

[JeteMc]

It would seem to me that this would be easier if the values were referenced from the Account sheet, as referencing the values from the in customer sheets will require either six different sets of formulas or using the INDIRECT function multiple times per formula.
This proposal adds two columns to the Accounts sheet.
The first column shows transfers and is manually populated.
The second column shows a running balance per customer and is populated using: =IF(S1=S2,SUM(R1,P2)-O2,Q2)
On the "customer" sheet the debt and receivable columns are populated using: =SUMIFS(Account!O$2:O$22,Account!$S$2:$S$22,$H2,Account!$A$2:$A$22,">="&$P$2,Account!$A$2:$A$22,"<="&$Q$2)
The balance column is populated using: =IFERROR(INDEX(Account!R$2:R$22,AGGREGATE(14,6,(ROW(Account!R$2:R$22)-ROW(Account!R$1))/(Account!S$2:S$22=H2)/(Account!A$2:A$22<=Q$2),1)),0)
Let us know if you have any questions.

[Tacir]

Dear JeteMc hello,

The home page has resulted in transactions.I've done a few tries, the problem is not visible.It was a logical solution.
I will examine it a little more, say it was resolved according to the situation.


Yours truly

[Tacir]

Hello JeteMc,

This method is positive for sequential transactions.Transactions are mixed in the Accounting tab.
This method is negative, as customers are randomly entered with their names.

[JeteMc]

Two possibilities:
1. Convert the range on the account sheet into a table and keep the table custom sorted by customer2 (A to Z) and date (oldest to newest)
2. Convert the ranges on each of the individual customer sheets into tables and wright formula based on the customer name sheets as modeled in cells H2:J2 on the Customer sheet.
2a. For customer ABC the debt and receivables formula would be: =SUMIFS(ABC!O$2:O$5,ABC!$A$2:$A$5,">="&$O$2,ABC!$A$2:$A$5,"<="&$P$2)
2b. The balance formula would be: =IFERROR(INDEX(ABC!Q$2:Q$5,AGGREGATE(14,6,(ROW(ABC!R$2:R$5)-ROW(ABC!R$1))/(ABC!A$2:A$5<=P$2),1)),0)
2c. For the other customers replace "ABC" with that customers name.
Note that the reason to convert the ranges into tables is so the formulas will auto adjust when new rows are added.
Let us know if you have any questions.

[Tacir]

Hello Dear JeteMc,


I studied and applied to the formula.But the result of the values does not seem to cover the deadline
I wonder if I'm looking wrong ?

[JeteMc]

All formula references in the "Apple" row should be to the Apple sheet.
Try the following in cell J5 on the Customer sheet:

=IFERROR(INDEX(APPLE!Q$2:Q$6,AGGREGATE(14,6,(ROW(APPLE!Q$2:Q$6)-ROW(APPLE!Q$1))/(APPLE!A$2:A$6<=P$2),1)),0)

Let us know if you have any questions.

[Tacir]

JeteMc,

now it is OK.thank you so much. The problem has been resolved.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.