Adding cells in multiple sheets

[memoe]

Hello everyone :D

i currently have a workbook that i use to keep track of hours per two week period which is represented by the addition of a new sheet with that data. I have a main sheet that is used to tally hours and monies paid dynamically to give me the totals thus far. i am using Sum('sheet1:sheet2'!a1,'sheet2:sheet3'!a2,...etc) when there are changes to the row that an employee would be listed due to the addition or removal of an individual. i would like that to be more intuitive if it can be... for example... lookup the persons name on the column listed, look for row labeled Hours and add that value from each sheet. Any help on how to write that as a formula in a cell on the main tab would be appreciated.

**added example book

[quekbc]

It would help us a lot if you could post a sample spreadsheet, highlighting the issue at hand and showing the desired output. Remember to remove and sensitive data before attaching the spreadsheet.

To post attach a spreadsheet, click on Go Advanced, and look for Manage Attachments at the bottom.

[FDibbins]

Agree that a sample WB will greatly help.

This is teh basis of what you need to do...
1. Create a list of sheet names
2. Give that list a range name (I called it Tabs)
3. Use this formula, adjusted for your rangers...
=SUMPRODUCT(SUMIF(INDIRECT("'"&BldgTabs&"'!T6:T1000"),C794,INDIRECT("'"&BldgTabs&"'!M6:M1000")))

If you are familiar with excel formulas, you will see that this is essentially a SUMIF() function, using INDIRECT() to catch all the sheer names

[memoe]

thank you for the quick replies... i have look at that example before FDibbins but do not understand the range... what to change to what exactly

[FDibbins]

1. create a list of all sheet names
2. create a range name for that list
- highlight range, right-click into range/Define Name/call it Tabs
3. Use this as the "Refers To"
=OFFSET(Main!$J$4,0,0,COUNTA(Main!$J:$J),1)
Note, this is based on your list of sheet names being in column J, starting in row 4
Adjust this as needed
4. I used this formula, for Hours…
=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!B5:B1000"),B5,INDIRECT("'"&Tabs&"'!C5:C1000")))
Again, adjust the references as needed

Hope this helps

[memoe]

That did the trick and i understand that example... thanks so much for the quick replies/help.... you guys rock!!!