chetanojha
New Member
- Joined
- May 3, 2016
- Messages
- 20
Dear Forum Members,
I have a situation where I receive a lot of files from different sales depots to me on daily basis. These files are identical in nature. All files have three worksheets namely Depot Name, Operator and Order_Details. Only one column (in “Order_Details” worksheet) keeps changing. This “Order_Details” worksheet typically looks like below:
<tbody>
</tbody>
Now, I have a master excel sheet (Consolidate_Orders.xls) where I load all this information, received above from Order_Details worksheet, manually at the moment. This Consolidate_Orders.xls workbook looks like below. This workbook consolidates all the files which I receive daily from different depots.
<tbody>
</tbody>
My requirement is to have a button in the Consolidate_Orders.xls workbook/worksheet – which will then open a dialog box to ask me which file I need to import into the Consolidate_Orders.xls workbook. I will then select London_Depot file (as shown above). Once London_Depot file is selected, data from the worksheet “ORDER_DETAILS” (column name “VALUES” ) will be copied and transpose pasted in the end of the Consolidate_Orders.xls file
I checked this forum and also googled it. There are lot of material.. but I cannot make much sense of it.
Any help would be appreciated.
Thanks a lot
I have a situation where I receive a lot of files from different sales depots to me on daily basis. These files are identical in nature. All files have three worksheets namely Depot Name, Operator and Order_Details. Only one column (in “Order_Details” worksheet) keeps changing. This “Order_Details” worksheet typically looks like below:
Entity | Description | Values |
Deport Name | Name of the Depot | London |
Truck Entry | Time when truck entered | 01/12/2017 01:45:34 |
Truck Exit | Time when truck exited | 01/12/2017 04:45:34 |
Total Time Take | Total Time in despatch | 03:00:00 Hours |
Operator | Name of the Operator | Alan |
Operator Average | Average time of the operator | 2.45 hours |
Total Weight | Weight of the truck when exit | 11 Tonnes |
Total Sale value | Total Sale Value | $15000 |
Order Status | Is Order Completed | Completed |
<tbody>
</tbody>
Now, I have a master excel sheet (Consolidate_Orders.xls) where I load all this information, received above from Order_Details worksheet, manually at the moment. This Consolidate_Orders.xls workbook looks like below. This workbook consolidates all the files which I receive daily from different depots.
Deport Name | Truck Entry | Truck Exit | Total Time Taken | Operator | Operator Average | Total Weight | Total Sale value | Order Status |
London | 01/12/2017 01:45:34 | 01/12/2017 04:45:34 | 03:00:00 Hours | Alan | 2.45 hours | 11 Tonnes | 15000 | Completed |
Manchester | 01/12/2017 01:45:34 | 01/12/2017 04:45:34 | 03:00:00 Hours | Michael | 2.45 hours | 12 Tonnes | 1800 | Completed |
Swindon | 01/12/2017 01:45:34 | 01/12/2017 04:45:34 | 03:00:00 Hours | Elaine | 2.45 hours | 13 Tonnes | 2000 | Completed |
Swansea | 01/12/2017 01:45:34 | 01/12/2017 04:45:34 | 03:00:00 Hours | Julie | 2.45 hours | 14 Tonnes | 50000 | Completed |
<tbody>
</tbody>
My requirement is to have a button in the Consolidate_Orders.xls workbook/worksheet – which will then open a dialog box to ask me which file I need to import into the Consolidate_Orders.xls workbook. I will then select London_Depot file (as shown above). Once London_Depot file is selected, data from the worksheet “ORDER_DETAILS” (column name “VALUES” ) will be copied and transpose pasted in the end of the Consolidate_Orders.xls file
I checked this forum and also googled it. There are lot of material.. but I cannot make much sense of it.
Any help would be appreciated.
Thanks a lot