I need a formula

[ismat1950]

Hello,

I need your help guys,

I have a Excel file which is daily debitor report. This file has two sheets . Sheet 1 and Sheet 2. 

So, I want the data in the sheet 2 (here sales and bank) to be added to sheet 1 automatically. One of my friend applied a VBA code for this and it works ( attached file possess this VBA code). However, I want it with formula. I hope anyone can help me with this situation. 

[#2 · Accepted solution]

@ismat1950 Allow me to ask you a question. Why such a complicated approach where a list of transactions needs to broken up in small blocks of daily information? Probably something you need to recreate every month with no way to keep track of e.g. the whole year in one schedule.

I would transform the transactions list in Sheet2 to a structured Excel table and keep on adding transactions, day-by-day. Then you can build all sorts of reports off that table with Pivot Tables. No VBA, no complicated formulae needed. If you are not familiar with Pivot Tables you can learn more about them in the attached link. One of many on-line resources. 

https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bf... 

[#3]

@Riny_van_Eekelen I really don't know how to do it with Pivot Table. I tried to create but I couldn't.

I hope you could help me.

[#4]

@ismat1950 Perhaps the attached file (Sheet2) will help you get started with another way of collecting transactional data and using pivot tables to create different reports. Search the web for "pivot table excel" and you'll find many links that can help you discover them.

The table in col B:E is a structured table. Continue typing the date of next transaction in B15 and the table will automatically expand. On the Data ribbon you'll find a Refresh button. Press it and all pivot tables (from col H and to the right) will refresh to include the new transaction(s).

[#5]

@Riny_van_Eekelen actually it helps. However, one more information I need to add to the table which is current outstanding balance for each customer. How can I do It again I don't know (. 

[#6]

@ismat1950 Not sure I follow. Where do you want to add the current balances?

The transactions table has the opening balances, all sales amounts and incoming funds from the bank. These three together make-up the current outstanding balance, as shown on the subtotal rows in the grand total column of the pivot tables. Of course, you can create pivot table with just the company as the row header and the amount in the value field. That will result in a list of current balances per company. Please clarify it that is not what you want. 

[#7]

Sorry Mr Riny van Ekelen. I realized where is my mistake. I did not put minus before the bank amount : ) thats why it was incorrect overall balance.
Thanks a lot Mr. I appreciate your help.