Forum Discussion
I need a formula
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.
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.
6 Replies
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.
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.
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).
