conditional sum across worksheet pages

Hello,

I'm new and greatly appreciate any help you can provide. I've scoured the internet for a solution to a problem I have (I most likely created the problem).

I have been tasked with creating a workbook to budget the utility bills for each of our four districts. I have created running totals and rolling averages for each spreadsheet. The totals and averages are in the same cell on each of the sheets. The nature of the business we do, there can be a new account added at any time, so I created a macro to add a sheet preformatted with the proper table. If we weren't adding new sheets this would be a simpler process, and I have been trying my damnest to define a range of sheets (no cells) in order to allow me to create conditional totals on my front page.

So, for the formula I need a total of the values in cells K13 if that page contains the word CPUH in cell h4. If the word "CPUH" is not written in cell H4 (either blank or a different phrase) I would like that page's value omitted from the total.

Ive created two blank pages titled "Startsheet" and "Endsheet" that will bookend my range of worksheets. I thought I could just define a range, ala "define name, ='Startsheet:Endsheet'! " but the error "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference."

I feel like this is possible. I'm just stuck. I need this workbook to be dummyproof so I can hand it off to someone else and they won't have to be able to troubleshoot, add formulas, etc. They just enter this months bill total, it automatically updates the front page with the up to date averages and totals.

I've seen formulas that should work . . .

=SUMPRODUCT(SUMIF(INDIRECT("`"&SheetList"`!H4"),"CPUH",INDIRECT("`"&SheetList"`!K13")))

. . . where somehow sheetlist is magically defined without the terror (error) message above.

Any help you can provide would be greatly appreciated.

Best,

Philip

Thank you Glenn. Unfortunately I'm not sure the method for selecting a range will work for me. I need to find a way to create a defined range of sheets that can somehow include each new sheet my macro creates, so a dynamic range. I may just need to split my workbook into 4 separate workbooks based on the district, but ideally I could have this all in one sheet. So close!

No sir, you have the point. I may be missing something and I will investigate further and report back. Many thanks for the assistance!

Glenn, I appreciate your patience with me. So what you're saying is that in the example you provided, Book2, when I create new sheet the list of sheets in column AA should be automatically updating? Its not working for me. And I definitely have macros enabled.

I'm sure there is a way for me to create a VBS to make a dynamic list of sheet names that populate there, so you've definitely got me on the right track. Many thanks!

I'll take a look later when I get a free moment. I'm glad I'm not loony! Thank you!!!

Yessssss!!!!!!! Thank you!!!! :-D