Formula Help on referencing other sheets

[Waidesworld]

PROBLEM
I have multiple sheets with data on them and I want the main sheet to compare what is in say A2 and search all other sheets for the same A2 value. If there is a match I want to pull corresponding data say C2 from the Sheet to B2 on the Master.

ACTUAL PROBLEM
I am working on player values in fantasy baseball and want to pull the data from sub sheets to the master sheets by comparing the name and pulling relative values to the master.

Please address my pet hate, "a cable is loose, you lose your keys

 

[KenWright]

Do you mean look at A2 on all the other sheets and then return the sum of all the C2s where A2 matches that of A2 on your summation sheet?

Assuming so, and also further assuming that your sheets are named Sheet1, Sheet2, Sheet3 etc then in cell B2 on your summation sheet

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&{2,3,4,5}&"!A2"),A2,INDIRECT("Sheet"&{2,3,4,5}&"!C2")))

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[Waidesworld]

No on the other sheets teh same name might have moved as it is listed alphabetically and I am combining stats from 03 and 04, so say Aaron Hank would have gone from A2 on the "Master" to A3 on the "Outfielder" sheet

Please address my pet hate, "a cable is loose, you lose your keys

 

[KenWright]

How many sheets are we talking?

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[Waidesworld]

eleven sheets only, one is master then one for each position, issue is that when you list the master alphabetically the first sheet would be the catchers and unfortunately baseball doesn't have players play alphabetically by position!

Please address my pet hate, "a cable is loose, you lose your keys

 

[KenWright]

OK then try this, just substitute your sheet names for the abc, bcd, cde etc and change the ranges to suit

=SUMPRODUCT(SUMIF(INDIRECT({"abc","bcd","cde","def","efg","fgh","ghi","hij","ijk","jkl"}&"!A1:A30"),A2,INDIRECT({"abc","bcd","cde","def","efg","fgh","ghi","hij","ijk","jkl"}&"!C1:C30")))

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------