avaltewarehous
New Member
- Joined
- Nov 8, 2012
- Messages
- 38
Good evening,
I recently started at a new company, and they are using a rather cumbersome and time consuming spreadsheet system to complete a relatively simple task, and I would like to explore other routes for optimization of the process. The task is performed on a daily basis, and the object of the process is to reconcile the previous day's cash receipt and disbursement records between internal accounting records (from our database) and bank records. Currently, we use a query created in Showcase to retrieve all of the individual receipt/disbursement (r/d) records from the previous day for each of our (30?) bank accounts. Those records are dumped into an Excel sheet, and the user then creates a pivot table from the data to group the total receipts and disbursements (separately) by bank account number. Next, the summary info from the pivot table is transcribed (via direct human labor, no automation) into a second Excel workbook. This second workbook is created on a monthly basis, and contains 2 sheets for each bank account (60 sheets in total [2*30 accounts]): 1 for total daily receipts, 1 for total daily disbursements. Each sheet has a column which shows every day of that month along with the record of total daily receipts or disbursements (depending on which of the 2 sheets you are working in). It is in the column next to this date column where the user must manually transcribe the summary data from the pivot table each day - and with 60 sheets to enter each day, you can imagine how this process becomes time consuming very quickly.
My feeling is that the steps of dumping the data into an external workbook, creating the pivot table to summarize the data and manually transferring the summary data into the reconciliation workbook can all be eliminated, and that a macro can be created which automatically queries the table and returns the daily summary value into the desired cell. My issue is that I do not know how to embed a query into a macro in Excel. Please confirm my hypothesis, and, if there is a way, describe how it can be done. Thank you!
I recently started at a new company, and they are using a rather cumbersome and time consuming spreadsheet system to complete a relatively simple task, and I would like to explore other routes for optimization of the process. The task is performed on a daily basis, and the object of the process is to reconcile the previous day's cash receipt and disbursement records between internal accounting records (from our database) and bank records. Currently, we use a query created in Showcase to retrieve all of the individual receipt/disbursement (r/d) records from the previous day for each of our (30?) bank accounts. Those records are dumped into an Excel sheet, and the user then creates a pivot table from the data to group the total receipts and disbursements (separately) by bank account number. Next, the summary info from the pivot table is transcribed (via direct human labor, no automation) into a second Excel workbook. This second workbook is created on a monthly basis, and contains 2 sheets for each bank account (60 sheets in total [2*30 accounts]): 1 for total daily receipts, 1 for total daily disbursements. Each sheet has a column which shows every day of that month along with the record of total daily receipts or disbursements (depending on which of the 2 sheets you are working in). It is in the column next to this date column where the user must manually transcribe the summary data from the pivot table each day - and with 60 sheets to enter each day, you can imagine how this process becomes time consuming very quickly.
My feeling is that the steps of dumping the data into an external workbook, creating the pivot table to summarize the data and manually transferring the summary data into the reconciliation workbook can all be eliminated, and that a macro can be created which automatically queries the table and returns the daily summary value into the desired cell. My issue is that I do not know how to embed a query into a macro in Excel. Please confirm my hypothesis, and, if there is a way, describe how it can be done. Thank you!
