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