Dynamically changing cell's formula
 

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

Dynamically changing cell's formula
 

Put a date in A1, then use:

=SUMPRODUCT(N(INDIRECT(ROW(INDIRECT(1&":"&DAY(A1)) )&"!"&ADDRESS(75,12))))

So if A1 contains 12/25/2008, the above formula gives the same result as:
=SUM(1:25!L75)

This is Rosenfeld's formula posted in:


http://groups.google.com/group/micro...7 5412f9c06f0

--
Gary''s Student - gsnu200806


"CW" wrote:

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