Conditional extract from external worksheets
 

Hi

I have had a workbook foisted on me which is to be distributed to my
managers and it has 52 sheets in the form wk 1, wk2.....wk 52 etc. I need to
be able to pull in the weekly data for the workbooks they forward and these
will include all weeks. I have done stuff like this before using lookup but
that will only work if the data is in a continuous array I guess?

What I would like to be able to do on the collation sheet is put a week
number into a cell and have it populate the collation sheet with the totals
from the various returns. All the return sheets are structured identically,
I'm sure I could do this in a long winded and cack handed way but I'm hoping
there will be a nice, elegant and time saving way!!

All suggestions gratefully received!!

Peter

Perhaps something along these lines would provide some ideas ..

Let's assume 2 identical structure books returned by 2 managers,
Mgr1.xls and Mgr2.xls

In Mgr1.xls,
assume 2 sheets, named Wk1 and Wk2

In Wk1, in A1:B3:
------ Sales
Prod1 10
Prod2 20

In Wk2, in A1:B3:
------ Sales
Prod1 30
Prod2 40

In Mgr2.xls, you have 2 identical sheets,
named Wk1 and Wk2

In Wk1, in A1:B3:
------ Sales
Prod1 100
Prod2 200

In Wk2, in A1:B3:
------ Sales
Prod1 300
Prod2 400

-------------
In your Summary book
----------------
In Sheet2 (say)
Put in A1: Wk1
Copy down to A52
Name the range A1:A52 as : Week
(this is for the DV to be created in Sheet1's A1)

In Sheet1
---------
Assume the table set up below in cols A to D,
with a DV in A1, a "Total" label in B1, the filenames of each manager's book
listed across in C1, D1, etc, and the row headers: Prod1, Prod2, etc in A2
down, viz.:

<DV Total Mgr1 Mgr2
Prod1
Prod2

Set up a DV to select the week# in A1
Select A1
Click Data Validation
Select under "Allow:" : List
Put in "Source:" =Week
Click OK

Put in B1:
=SUM(OFFSET($C$1,ROWS($A$1:A1),,,COUNTA($1:$1)-2))

Put in C1:
=INDIRECT("'["&C$1&".xls]"&$A$1&"'!B"&ROWS($A$1:A1)+1)
Copy C1 across to D1

Select B1:D1, fill down

For the sample data:

If "Wk1" is selected in the DV droplist in A1
you'll get:

Wk1 Total Mgr1 Mgr2
Prod1 110 10 100
Prod2 220 20 200

And if "Wk2" is selected in A1, you'll get:

Wk2 Total Mgr1 Mgr2
Prod1 330 30 300
Prod2 440 40 400

Note: All the "slave" books: Mgr1.xls, Mgr2.xls need to be open for the
INDIRECT() formulas to work, otherwise you'll get #REF!

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"peter marsh" wrote:

Hi

I have had a workbook foisted on me which is to be distributed to my
managers and it has 52 sheets in the form wk 1, wk2.....wk 52 etc. I need to
be able to pull in the weekly data for the workbooks they forward and these
will include all weeks. I have done stuff like this before using lookup but
that will only work if the data is in a continuous array I guess?

What I would like to be able to do on the collation sheet is put a week
number into a cell and have it populate the collation sheet with the totals
from the various returns. All the return sheets are structured identically,
I'm sure I could do this in a long winded and cack handed way but I'm hoping
there will be a nice, elegant and time saving way!!

All suggestions gratefully received!!

Peter