Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save External Link Values | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) | |||
Conditional hiding of worksheets | Excel Discussion (Misc queries) |