Help with automatically updating the date with SUMIF statement

[Nerdy Suit]

I have two workbooks that have a separate sheet for each calendar day.

One workbook contains inventory numbers that are keyed in for each inventory location; no formulas are on this workbook.

The second workbook contains several SUMIF statements to add up the inventory from the first workbook. For example :

=SUMIF('[April 2012.xls]27'!$C$6:$C$37,"MILK",'[April 2012.xls]27'!$J$6:$J$37)

=SUMIF('[April 2012.xls]27'!$C$58:$C$63,"MILK",'[April 2012.xls]27'!$J$58:$J$63)

=SUMIF('[April 2012.xls]27'!$C$58:$C$63,"CREAM",'[April 2012.xls]27'!$J$58:$J$63)

=SUMIF('[April 2012.xls]27'!$C$6:$C$63,"OUTSIDE CREAM",'[April 2012.xls]27'!$J$6:$J$63)

So the second workbook totals up different parts of the first workbook according to if the words MILK or CREAM or OUTSIDE CREAM are found.

My problem:

I have to go into the SUMIF statement each day to change the date that the SUMIF statement is referencing. So when I do the inventory for April 27, I have to change the 27. Tomorrow, I will have to go into the formula and change the 27 to a 28; and so on.

Is there a way to do this automatically without needing to change the day every single time? FYI - each tab in the workbook is a separate day. So April 27 would be on the 27th tab; April 28 would be on the 28th tab; and so on.

[Bob Phillips]

You could use INDIRECT

=SUMIF(INDIRECT("'["&TEXT(TODAY(),"mmmm yyyy")&".xls]"&TEXT(TODAY(),"d")&"'!$C$6:$C$37","MILK",INDIRECT("'["&TEXT(TODAY(),"mmmm yyyy")&".xls]"&TEXT(TODAY(),"d")&"'!$J$6:$J$37")