Return amount values based on date and name criteria

Hi,

Looking for some help with my cash outflow report.

I have attached my file for easy reference.

Under Raw File sheet is a list of payments divided in a week's time (my report is every Monday so it covers transactions from Tuesday of the previous week till Monday that day). I incorporated two weeks reporting here. I should add the next weeks transaction at the bottom.

As you will see, under Ref column, the majority of payments starts with S and this pertains to "Subbie Payments". The other major group starts with C and these are "Creditor payments". P- for "Payroll" and then those highlighted in yellow which either has the Supplier's name or mere "Payment" as notes.

Now, what I need is for these details to automatically reflect on my Cash Outflow Sheet in the following manner. (Please note that my Cash Outflow sheet should capture the amounts of payments to which it belongs to for the same week).

1. All amount transactions with ref that starts in S will be reported under Subbie Payments.
2. All amount transactions with ref that starts in C will be reported under Creditor Payments.
3. All amount transactions with ref that starts in P- will be reported under Payroll Payments.
4. All amount transactions with numbers as reference and has "Payment' as Note as will be reported under Payments.
5. And lastly for all other transactions with number as reference - if the Supplier's Name is already in Column B in Cash Outflow Sheet, then it should just reflect the amount paid for it for that week. If in case it is a new one, then I can just manually add the name under column B but then the formulas under the date headers should automatically reflect the amount paid for it.

So all in all, I guess the formulas under the columns with the weekly dates as header should be able to lookup the amount paid for that Supplier or Type of payment (i.e based on my category above) and reflect it under the same week ended dates.

This is how I see my report to look like. But of course, if there is any other way, then maybe we can come up to it together. Basically, I just want the sheet to provide me the amounts paid for each Supplier or type or payments made within the week.

I have entered in navy blue font the supposed to be results in the Cash Outflow sheet.

Thank you so much for your time. It is greatly and highly appreciated.
Attached Files Attached Files

Hi nicky,

I think you are looking for attached report. I used two helper column (in rawfile tab P & Q column) for this don't delete that columns, and everyis populated based on your inputs

Krishna

Originally Posted by krishnakuma6

Hi nicky,

I think you are looking for attached report. I used two helper column (in rawfile tab P & Q column) for this don't delete that columns, and everyis populated based on your inputs

Krishna

Hi krishnakuma6,

Wow! this is SUPERB!!! Can't believe that this is actually possible. Really appreciate your time and effort. It really works! Thank you so much! Thank you! Thank you!