Automatically combining data from several worksheets

[Robert Hackett]

Hi,

I have several worksheets in a workbook - individual
quotations (one sheet for each product type).
Each sheet has standard information in each row - part
no., vendor, quantity, cost price, sales price etc

I am trying to find a way to combine the data from the
several worksheets/quotations into a master worksheet for
ordering i.e. combine the rows from the various sheets (if
quantity >0)

Any ideas or help appreciated

Robert

 

[Jason Morin]

One way is to list all the worksheet names in a column on
your master worksheet (if you have a lot of worksheets
there are ways of producing this list quickly). Then use a
formula with the function INDIRECT. For example, with
sheet names in col. A:

A B C
1 Sheet Part No. Price
2 Item1
3 Item2
4 Item3

With the Part No. in cell C1 on each sheet, put this in B2
and fill down:

=INDIRECT("'"&A2&"'!C1")

Do the same for the other product data and change the cell
reference.

HTH
Jason
Atlanta, GA