Dataset refresh from worksheets

wisemank

Board Regular
Joined
Jun 21, 2010
Messages
129
Hi there,

I have many worksheets that are used to track suppliers and I want to collect all into a dataset for a pivot table to refresh and summarize quickly. Each supplier sheet is named by the supplier and I wanted to copy each column in the supplier sheet and loop through the dataset sheet to refresh and or update. Below is data from one supplier starting always at Column A and ending at Column F. I wanted the dataset to reflect the supplier in column A (suppliers Worksheet name), then paste the information relative to each supplier.

Tool#W.O #Early off partsExternal
Buyoff
Homeline
Buyoff
Homeline Plant
1350541527612/30/20202/16/20212/23/2021Industrial Dr.
1350561527612/30/20202/16/20212/23/2021Industrial Dr.
176070153347/2/20219/15/202110/8/2021Industrial Dr.
176072153357/19/20218/6/20218/25/2021Industrial Dr.
17607915341N/A10/7/202110/21/2021Industrial Dr.
1760811534110/8/20219/29/202110/11/2021Industrial Dr.
176082/83153419/29/20219/8/20219/29/2021Industrial Dr.
176084/85153419/17/20219/17/20219/30/2021Industrial Dr.

Results:
ShopTool#W.O #Early off partsExternal
Buyoff
Homeline
Buyoff
Overall % Complete
Cleveland St.105057153458/19/20218/27/20219/3/202150%
Cleveland St.17608815341N/A8/6/20218/27/202150%
Cleveland St.17608915341N/A8/6/20218/27/202150%
Cleveland St.17609115341N/A9/6/20219/27/202125%
DieNamic17608715341N/A9/6/20219/27/202125%
DieNamic23701615368N/A11/2/202111/16/202125%
DieNamic23702415371N/A11/2/202111/16/202125%

Thanks,

Kip
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
You could use Power Query to get data & transform, then Append into one data set for pivot table.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top