Prev: date formating, creating unique calender
Next: If formula current/expired/blank
From: ucastores on 28 Feb 2010 18:10
I am trying to add a series of numbers in different worksheets(same
spreadsheet) based upon the date. I don't want the future numbers in the
total until the date they are associated with is passed. i.e. in A1 of each
worksheet is 200. Each worksheet is titled by date (Mar 5, Mar 19, etc) I
want the running total to reflect the amount to the current date. If it's
March 18th, the totals would be only thru the worksheet of Mar 5. Any help
is appreciated.
From: JLatham on 28 Feb 2010 20:08
My solution needs 2 cells, one of which may already be in use:

You need a cell on each of the dated sheets that has that date in it. That
is, if the sheet's name is May 5, then some cell on the sheet needs to have
that date in it (with the year also, as 5/1/2010). Lets assume that is in B1
for the example here.

Then you can set up a helper cell on those sheets (use same cell on each
sheet)
=IF(B1<=Today(),A1,0)
Again, for this example, lets say that is in cell C1.

Back on your rollup sheet you can then put a formula like this:
=SUM('Jan 1:Dec 31'!C1)

the Jan 1 sheet should be the first sheet in the entire group, and the Dec
31 sheet should be the last. Search Excel help for the phrase:
Refer to the same cell or range on multiple sheets




"ucastores" wrote:

> I am trying to add a series of numbers in different worksheets(same
> spreadsheet) based upon the date. I don't want the future numbers in the
> total until the date they are associated with is passed. i.e. in A1 of each
> worksheet is 200. Each worksheet is titled by date (Mar 5, Mar 19, etc) I
> want the running total to reflect the amount to the current date. If it's
> March 18th, the totals would be only thru the worksheet of Mar 5. Any help
> is appreciated.
 | 
Pages: 1
Prev: date formating, creating unique calender
Next: If formula current/expired/blank