Dynamic Formula to reference sheets

[p_s]

I have 13 sheets, 1 of which is a summary sheet that references data with sumif formulas. I need to set this up for for over 100 workbooks with a similar format..

Sheet Names are Listed in Row 1 of the Summary Sheet as shown below.

Summary	1-Jan	2-Feb	3-Mar	4-Apr	5-May	6-Jun	7-Jul	8-Aug	9-Sept
Category 1
Category 2
Category 3
etc.

How can I make my formulas dynamic.

Formula needed: =SUMIF('1-Jan'!$E$2:$E$900,$A2,'1-Jan'!$D$2:$D$900)

I tried using the Indirect function and it did not work. I tried it two different ways

1. =SUMIF(INDIRECT(B$1&"!$E$2:$E$900"),$A2,INDIRECT(B$1&"!$D$2:$D$900"))
2. =SUMIF(INDIRECT(B$1&"!$E$2"):INDIRECT(B$1&"!$E$900"),$A2,SUMIF(INDIRECT(B$1&"!$D$2"):INDIRECT(B$1&"!$D$900")))

I am not sure what I am doing wrong and how I can fix this. Please help. I have been trying to scour previous threads but I can't seem to find anything that helps. It only works if I manually do it without the cell reference to the sheet name.

 

[p_s]

=SUMIF(INDIRECT("'"&B$1&"'!$E$2:$E$900"),$A2,INDIRECT("'"&B$1&"'!$D$2:$D$900"))

I used this formula and it seems to have worked. *fingers crossed*

 

[Fluff]

How about

=SUMIF(INDIRECT("'"&B$1&"'!$E$2:$E$900"),$A2,INDIRECT("'"&B$1&"'!$D$2:$D$900"))