G
Guest
Hi,
I've got a monthly workbook showing daily delivery details with each day on
a separate tab, but I need a summary (e.g. the same customer may appear on
more than one tab in the month and I need a summary of all their deliveries).
I tried to create a pivot table with a consolidated data source but this
didn't work so now I'm thinking I may need to write a macro. I can't change
the structure of the spreadsheet as we receive a standard format from the
courier.
Any suggestions how I approach this or whther my requirements below are even
possible!?
Ideally I need to:
- Display an input box for the user to enter an order number (found in
column b of all worksheets).
- Search column b in all worksheets (unique number so will either find 1
value or return a fail).
- Find the corresponding customer name from column c, copy that customer name.
- Add a new worksheet at the end of all other current worksheets and paste
the customer name.
- Search column c in all worksheets and where a match is made copy that
whole row and paste into the new summary worksheet.
- Repeat until all rows on all worksheets for that customer have been found
and entered into the summary tab.
THANK YOU!
Mel
I've got a monthly workbook showing daily delivery details with each day on
a separate tab, but I need a summary (e.g. the same customer may appear on
more than one tab in the month and I need a summary of all their deliveries).
I tried to create a pivot table with a consolidated data source but this
didn't work so now I'm thinking I may need to write a macro. I can't change
the structure of the spreadsheet as we receive a standard format from the
courier.
Any suggestions how I approach this or whther my requirements below are even
possible!?
Ideally I need to:
- Display an input box for the user to enter an order number (found in
column b of all worksheets).
- Search column b in all worksheets (unique number so will either find 1
value or return a fail).
- Find the corresponding customer name from column c, copy that customer name.
- Add a new worksheet at the end of all other current worksheets and paste
the customer name.
- Search column c in all worksheets and where a match is made copy that
whole row and paste into the new summary worksheet.
- Repeat until all rows on all worksheets for that customer have been found
and entered into the summary tab.
THANK YOU!
Mel