Hi

I have the following scenario:

I have a set of individual sheet that represent sales data for each day
(sheet named 1 to 31)
I have a consolidation sheet that consolidates month-to-date sales data from
sheet 1 to 31
I have a date field that user may change.

Say user enters Oct 11 2008, the consolidation sheet should sum all the data
from sheet 1 to 11.

One of the formula on the consolidation sheet is =SUM('1:31'!L75) where L75
is the address of quantity sold across all of the sheets.

Unfortunately, this formula does not update depending on the date I enter
(as it always sum all the sheets from 1 to 31).

I need a way to dynamically change the sheet reference in the formula if it
is at all possible. I have already looked at Indirect and I don't believe it
would help me (because I am using a formula rather than R1C1 address). I
could use Excel Macro to dynamically assign the formula. I am trying to
avoid VBA if possible due to security restrictions imposed.

Any suggestions?

Thanks in advance