Indirect() between multiple sheets?

I am trying to create a 3d sumif via sumproducts/indirect. I have figured out how to do so, but I have run into another problem. See below for my function.

=IF(K1="","",SUMPRODUCT(SUMIF(INDIRECT("'" & {"X", "Y", "Z"} & "'!A:A"),A1,INDIRECT("'" & {"X", "Y", "Z"} & "'!K:K"))))

where: X, Y, Z are the sheets names.

I have more than those three sheet names and will likely add more to the workbook. My question is: Is it possible to do an indirect across multiple sheets without listing them out one by one. I've tried adding a page at the beginning and at the end and adding "start:end" to the function. However, this does not seem to work. If anyone has a solution I would appreciate it.

Thanks,
Chris

My sheets are actually organized as dates, so renaming them isn't an option.

A little background on what I'm trying to do.

My workbook essentially a time sheet for me to keep track of all my projects and the hours I work each day. Essentially, it is organized as such:

A=Project

B=Mon
C=Tues
D=Wed
E=Thurs
F=Fri
G=Sat
H=Sun

I=Weekly Total
J=Total to DATE

J is the column for which I am developing the formula. I want it to sum all time worked on project A1 and output in J1. The reason I cannot sumif on the page and then just 3d sum is because the I am adding new projects all the time. Also, projects are not always on the same line week to week.

Originally Posted by ctasich

My question is: Is it possible to do an indirect across multiple sheets without listing them out one by one. I've tried adding a page at the beginning and at the end and adding "start:end" to the function. However, this does not seem to work. If anyone has a solution I would appreciate it.

Thanks,
Chris

Yes it is possible But don't have the formula in the same column where trying to reference from or you will get a circular reference error.

Select C2

Insert > Name > Define

Name: Shts

Refers to:

=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,255)

Click Ok

Then try...

=SUMPRODUCT(SUMIF(INDIRECT("'"&Shts&"'!A2:A10"),A2,INDIRECT("'"&Shts&"'!B2:B10")))

Hope this helps!