Can anyone help with this multiple sheet 3d query?

chrisw

New Member
Joined
Feb 23, 2004
Messages
5
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. :oops:
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

chrisw

New Member
Joined
Feb 23, 2004
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,315
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top