doublejtacoma
New Member
- Joined
- Oct 27, 2013
- Messages
- 4
Hello there,
I seek your help. I have scoured this forum, along with a few others, as well as some YouTube videos, and I can't seem to find out exactly what I am looking for.
I am looking to some data consolidation; please allow me to elaborate on what I am trying to achieve. What I have is a daily worksheet template that I use at work each day. So I end up with 365 worksheets per year, and that's fine for my record keeping, but what I am interested in doing is consolidating a month's worth of daily sheets into one Monthly worksheet. To simplify this consolidation, I am only interested in copying data from a few cells from each daily sheet. Here is a sample of the Daily Template:
Excel 2002
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
The objects highlighted in green are the cells I am interested in consolidating data from. The first one at the top is where I would type in the current date, but I think date information may be obtainable from the file name.
I am trying to achieve a consolidated worksheet that would look something like this:
Excel 2002
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
One thing to note is that some sort of a paste-->tranpose function to get the items from being listed vertically to being listed horizontally (or vice versa (I'm dyslexic)) would be very nice, but not necessary if listing dates horizontally is easier. Doesn't matter to me, I am just looking to create some charts, and do some year to year comparisons. The latter will be more useful and convenient than the way I have been doing it now (opening multiple worksheets and comparing one by one).
I should also mention, that I use an organized folder structure and a simple naming convention. The files are named [mmm dd] and placed into [Month yyyy] Folders. For example October 2013 Folder would contain files Oct 1.xls,Oct 2.xls....Oct 31.xls
I was hoping to generate one consolidated month report by applying a macro to each month folder, and like I said earlier, the date could be grabbed from the file name itself.
Let me know if you think you guys can help,
I appreciate all of your time, and want thank you in advance,
Cheers!
I seek your help. I have scoured this forum, along with a few others, as well as some YouTube videos, and I can't seem to find out exactly what I am looking for.
I am looking to some data consolidation; please allow me to elaborate on what I am trying to achieve. What I have is a daily worksheet template that I use at work each day. So I end up with 365 worksheets per year, and that's fine for my record keeping, but what I am interested in doing is consolidating a month's worth of daily sheets into one Monthly worksheet. To simplify this consolidation, I am only interested in copying data from a few cells from each daily sheet. Here is a sample of the Daily Template:
Excel 2002
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | COMPANY XYZ | ||||||||
| 2 | DAILY SALES REPORT DATE: | ||||||||
| 3 | DESCRIPTION | ACCOUNT | DEBIT | CREDIT | |||||
| 4 | |||||||||
| 5 | BANK DEPOSIT | 1130 | 0.00 | ||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | POS CREDIT | 1210 | 0.00 | ||||||
| 9 | |||||||||
| 10 | POS DEBIT | 1205 | 0.00 | ||||||
| 11 | |||||||||
| 12 | LOTTERY PAID OUT | 4176 | 0.00 | ||||||
| 13 | |||||||||
| 14 | CONFECTIONARY PAID OUTS | 4166 | 0.00 | ||||||
| 15 | |||||||||
| 16 | |||||||||
| 17 | |||||||||
| 18 | MAINTENANCE PAID OUT | 5820 | 0.00 | ||||||
| 19 | |||||||||
| 20 | |||||||||
| 21 | |||||||||
| 22 | VEHICLE EXP PAID OUT | 5580 | |||||||
| 23 | GST on Paid Outs | 1270 | 0.00 | Daily Agency Settlment | |||||
| 24 | Debit | Credit | |||||||
| 25 | Bank 1130 | ||||||||
| 26 | ESSO EXTRA REDEM | 5445 | 0.00 | Interac 1205 | 0.00 | ||||
| 27 | Credit 1210 | 0.00 | |||||||
| 28 | CHARGES | Fuel 4022 | 0.00 | ||||||
| 29 | Gift Card | ||||||||
| 30 | C/c Dsc 5315 | 0.00 | |||||||
| 31 | |||||||||
| 32 | US$ DEPOSIT | 1080 | 0.00 | ||||||
| 33 | |||||||||
| 34 | |||||||||
| 35 | US EXCHANGE | 5300 | 0.00 | ||||||
| 36 | |||||||||
| 37 | DRIVEAWAYS | 5510 | |||||||
| 38 | |||||||||
| 39 | CREDITS | ||||||||
| 40 | AGENCY FUEL SALES | 4022 | |||||||
| 41 | CONFECTIONARY SALES | 4160 | |||||||
| 42 | LOTTERY SALES | 4170 | |||||||
| 43 | LOTTERY REFUNDS | 4176 | |||||||
| 44 | GIFT CARDS (prepaid card purchases) | 4165 | |||||||
| 45 | GST COLLECTED | 2070 | |||||||
| 46 | |||||||||
| 47 | TOTALS | 0.00 | 0.00 | ||||||
| 48 | |||||||||
| 49 | CASH OVER/SHORT | 5380 | 0.00 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Daily Template
The objects highlighted in green are the cells I am interested in consolidating data from. The first one at the top is where I would type in the current date, but I think date information may be obtainable from the file name.
I am trying to achieve a consolidated worksheet that would look something like this:
Excel 2002
| A | B | C | D | |
|---|---|---|---|---|
| 1 | October, 2013 | |||
| 2 | Date | AGENCY FUEL SALES | CONFECTIONARY SALES | LOTTERY SALES |
| 3 | October 01 | |||
| 4 | October 02 | |||
| 5 | October 03 | |||
| 6 | October 04 | |||
| 7 | October 05 | |||
| 8 | October 06 | |||
| 9 | October 07 | |||
| 10 | October 08 | |||
| 11 | October 09 | |||
| 12 | October 10 | |||
| 13 | October 11 | |||
| 14 | October 12 | |||
| 15 | October 13 | |||
| 16 | October 14 | |||
| 17 | October 15 | |||
| 18 | October 16 | |||
| 19 | October 17 | |||
| 20 | October 18 | |||
| 21 | October 19 | |||
| 22 | October 20 | |||
| 23 | October 21 | |||
| 24 | October 22 | |||
| 25 | October 23 | |||
| 26 | October 24 | |||
| 27 | October 25 | |||
| 28 | October 26 | |||
| 29 | October 27 | |||
| 30 | October 28 | |||
| 31 | October 29 | |||
| 32 | October 30 | |||
| 33 | October 31 |
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Target Data Sheet
One thing to note is that some sort of a paste-->tranpose function to get the items from being listed vertically to being listed horizontally (or vice versa (I'm dyslexic)) would be very nice, but not necessary if listing dates horizontally is easier. Doesn't matter to me, I am just looking to create some charts, and do some year to year comparisons. The latter will be more useful and convenient than the way I have been doing it now (opening multiple worksheets and comparing one by one).
I should also mention, that I use an organized folder structure and a simple naming convention. The files are named [mmm dd] and placed into [Month yyyy] Folders. For example October 2013 Folder would contain files Oct 1.xls,Oct 2.xls....Oct 31.xls
I was hoping to generate one consolidated month report by applying a macro to each month folder, and like I said earlier, the date could be grabbed from the file name itself.
Let me know if you think you guys can help,
I appreciate all of your time, and want thank you in advance,
Cheers!
