Can anyone help with this multiple sheet 3d query?

[chrisw]

Hi, I have a workbook with a number of sheets.each sheet has an identifier in cell a1 (like an employees name), also in the 'a' column is a list of letters a - z and in the 'b' column number 1-26.

I have been using a pre-defined formula to go to sheet1 pick up the cell a1 reference and then a sumif to find the number for a specific letter i.e. z = 26. and then another for sheet 2 and then another for sheet 3 ad infinitum....

This has worked fine but the number of sheets changes on a monthly basis. and writing the formulaes is becoming too time consuming.

Can any one think of a way to write this so that my master sheet is automatically updated when a new worksheet is added?

(the letter for the search will always be the same so always z in this case)

Thank you in advance for any help, I am an excel expert but a VBA novice if that makes sense.

 

[Aladin Akyurek]

What formula are you using currently, restricted to a single sheet?

 

[chrisw]

Currently
manually going through the sheets and adding the identifies in A1 in a list

Bill
Bob
Fred
etc


then next to each one =SUMIF(bill!a2:a100,"z",Bill!b2:b100), which was fine whilst the positions were stable but obivously if craig joins and fred leaves it soon becomes hellish to maintain.

It looks like this

Bill 3
Bob 4
Fred 4


thanks.