Combine data from ALL worksheets in different workbook

nicars7

New Member
Joined
Apr 11, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello,

I've been working on a new template and excel sheet for the quality inspection of fresh carrots we receive.

Everytime we receive a load, we work on a Excel sheet to fill in our quality inspection. So every load has its own sheet based on a template in the same workbook. The name of the sheet always shows the name of our PO (e.i. 20F204 , 20J204 ) which is entered in cell B12 of our the sheet.

Now I'd like to use these sheets for production planning and quality monitoring. I need to create a separate workbook that pulls info from ALL sheets of our quality inspection workbook in a table/list. The source workbook is on a shared drive on the internal server.

I need to pull the cell data (text & numbers) by position of every sheet in the source workbook and make it a list I can use for reference.

From:

SHIPPER REF #:21054DELIVERY:4/16QUANTITY:28 bins
SAMPLE 1SAMPLE 2TOTAL
25lbs / 11340 grams25lbs / 11340 grams50lbs / 22680 grams
Defects No 1Defects No 2 and ungradedDefects No 1Defects No 2 and ungradedDefects No 1Defects No 2 & ungradedTotal Defects
DEFECTWeight (gm)%Weight (gm)%Weight (gm)%Weight (gm)%%%%
Curve3142.77%20.02%3142.77%20.02%5.54%0.04%5.57%
Crack/Split6585.80%00.00%1681.48%0.00%7.28%0.00%7.28%
Broken root
0​
0.00%
0​
0.00%0.00%0.00%0.00%0.00%0.00%
Insect bite
0​
0.00%
0​
0.00%0.00%0.00%0.00%0.00%0.00%
Broken crown2492.20%00.00%2492.20%0.00%4.39%0.00%4.39%
Long stem (1" & +)
0​
0.00%
0​
0.00%0.00%0.00%0.00%0.00%0.00%
Sunburn
0​
0.00%
0​
0.00%0.00%0.00%0.00%0.00%0.00%
Black
547​
4.82%
0​
0.00%0.00%0.00%4.82%0.00%4.82%
Decay8577.56%00.00%2522.22%0.00%9.78%0.00%9.78%
Size under ¾"
354​
3.12%
0​
0.00%0.00%0.00%3.12%0.00%3.12%
TOTAL297926.27%20.02%9838.67%20.02%34.94%0.04%34.97%

To:
Shipper ref #Curve wtCurve %Total wtTotal %Delivery date


Consolidate doesn't let me import text, and even then I'd have to do every sheet/cell one by one. And would need to add sheets as I go.

Hoping this is clear for someone to provide some help, I've been looking on google and youtube, but nothing points to how to import data from all sheets dynamically per cell position in a simple table.

Thanks
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Forum statistics

Threads
1,141,858
Messages
5,709,039
Members
421,608
Latest member
jking1

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
Top