How do I combine data from several different worksheets in Excel?

[Guest]

I’m trying to create an Order in Excel from data appearing on about 10
spreadsheets in the same workbook. Each spreadsheet contains part numbers,
descriptions and prices for specific products. A quantity will be entered
next to each line item that is required for the order. How do I get Excel to
find the order quantities, part nos., descriptions and prices on each
spreadsheet and create a consolidated order? Is Excel the best program for
this or should I be using Access?

 

[Frank Kabel]

Hi
I would use Access for this

 

[Arvi Laanemets]

Hi

And different sheets are for different sellers or departments or whatever
units?

You can create an consolidation table on separate sheet. P.e. when you have
on every sheet (Dep1, Dep2, ..., Dep10) a table with columns PartNo,
Description, Quantity, every sheet is for one department, and you are sure
there never will be more then 2000 entries for department, then:

Create a sheet (Summary) with columns Department, PartNo, Description,
Quantity
Into cell A2 enter the formula
=IF(B2="","","Department1")
Copy the formula to range A2:A1000
Into cell B2 enter link
=IF(Dep1!A2="","",Dep1!A2)
Copy the formula to range B22000
Into cell A2001 enter the formula
=IF(B2001="","","Department2")
Into cell B2001 enter link
=IF(Dep1!A2="","",Dep1!A2)
Copy both formulas like above down to row 4000
etc. for all departments.

You get a consolidated table with gaps in it. It'll be a serious drawback
for some funcktions, you probably want to use. You can avoid these problems,
defining a fixed named range, p.e. SummaryTable
=Summary!$A$1:$D$20000
, adding a sheet p.e. Summary2, and creating an ODBC query to get all data
from SummaryTable where Department <> "".

Now you can refer in your formulas to data on sheet Summary2.
Btw., you can hide both summary sheets - no need for users to access them.

PS. Why have you input on 10 different sheets at all. Or keep them all on
one single sheet and use report sheet to retrieve data for selected
department, or give a separate workbook to every department (so they can all
work at same time, without any problems). My example for consolidating works
for latter case too.



Arvi Laanemets

 

[Guest]

Thanks for your input, I'll give it a shot.
Barbara