Need to link/combine 5 worksheets into 1 (inventory nightmare)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 5 worksheets of inventory for 5 different depts.
All have only 3 columns - PART NO / DESC / PRICE
Each dept updates their worksheet daily.
How can I link all 5 worksheets into 1 common worksheet with same 3 columns
so I have a total inventory for whole company. Common worksheet should update
itself when any dept. worksheet is updated.
 
I would vote for a single inventory sheet with a fourth column, DEPARTMENT.

Vaya con Dios,
Chuck, CABGx3
 
You might be able to use MS Query to consolidate Excel ranges from your
multiple wkbks/wkshts. This also works for consolidating data from the
active workbook (Just save it first so Excel can find it):

My example uses 5 ranges named in 5 different workbooks.
(Each ranges contain 4 columns: Dept, PartNum, Desc, Price)

Assumptions:
-The data in each wkbk is structured like a table:
--->Col headings (Dept, PartNum, Desc, Price)
--->Columns are in the same order.

-The data in each wkbk must be named ranges.
--->I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc
--->You may use the same range name in different wkbks.

(Note: MS Query may display warnings about it's ability to show the query
....ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)Data>Import External Data>New Database Query Databases: Excel Files

-Browse to one of The files, pick The data range and columns to import.
--->Accept defaults until the next step.

-At The last screen select The View data/Edit The Query option.

-Click the [SQL] button

-Replace the displayed SQL code with an adapted version of this:

SELECT *
FROM `C:\Inventories\Dept1111`.rng1111Data
union all
SELECT *
FROM `C:\Inventories\Dept2222`.rng2222Data
union all
SELECT *
FROM `C:\Inventories\Dept3333`.rng3333Data
union all
SELECT *
FROM `C:\Inventories\Dept5555`.rng4444Data
union all
SELECT *
FROM `C:\Inventories\Dept5555`.rng5555Data

Return the data to Excel.

Once that is done....to get the latest data just click in the data range
then Data>Refresh Data.

You can edit the query at any time to add/remove data sources and/or fields.

Something you can use?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Back
Top