Formula with multiple worksheets

[Guest]

I have multiple worksheets and want to be able to type in a district name in
Cell C1-sheet 1 and have it pull the corresponding ISD infomration for
payroll, Premium, and fixed cost for years 03-04 from sheet 2 into sheet1
for cells B5 for 03-04 payroll, C5 for 03-04 premiums, and D5 for 03-04 for
fixed cost. Also need the formula to do the same thing for 04-05 in sheet 1
from sheet 3


Sheet 1
Cell A B C D
1
2 Payroll Normal Premium Fixed Cost
3
4 2003-04
5 2004-05

Sheet 2

Cell A B C D E
1
2 03 District 03 Payroll 03 NP 03 Max LF 03 Fixed Cost
3 AISD 230846 4999 2264 1200
4 BISD 25171 512 108143 29271
5 TISD 12913 3507 13022 821

Sheet 3
Cell A B C D E
1
2 04 District 04 Payroll 04 NP 04 Max LF 04 Fixed Cost
3 AISD 5555555 44444 33333 22224
4 BISD 25411 51287 10843 2971
5 TISD 174213 350 132 891

 

[Guest]

One way ..

Try this sample file which illustrates:
http://cjoint.com/?dplDV6ZFvn
Extracting from multiple shts.xls
(.. savefile's upload is down ..)

In Sheet1,

Insert a new col A, enter the sheetnames in A3 down.

D1 will house the input for the district, viz.: BISD, TISD, etc
(In the sample, just select from the DV droplist in D1)

Then just place in C3:
=OFFSET(INDIRECT("'"&$A3&"'!A2"),MATCH($D$1,INDIRECT("'"&$A3&"'!A:A"),0)-2,COLUMN(A1))
Copy C3 across to F3, fill down. Hide away cols A and E, and you would have
exactly the ops set up that you want.

 

[Guest]

Here's a more enduring link:
http://www.savefile.com/files/555084
Extracting_from_multiple_shts.xls

---