Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to link/combine 5 worksheets into 1 (inventory nightmare)
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to link/combine 5 worksheets into 1 (inventory nightmare)
I would vote for a single inventory sheet with a fourth column, DEPARTMENT.
Vaya con Dios, Chuck, CABGx3 "DiaboluS" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to link/combine 5 worksheets into 1 (inventory nightmare)
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)DataImport External DataNew 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 DataRefresh 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 "DiaboluS" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheets moved or deleted by unknown means! | Excel Discussion (Misc queries) | |||
Associating data across worksheets from an inventory that's sorted | Excel Discussion (Misc queries) | |||
Changing a Link Mid-way Across Worksheets | Excel Worksheet Functions | |||
Merge Worksheets | Excel Worksheet Functions | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |