need excel worksheet help, running total based on date

U

ucastores

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.
 
J

JLatham

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top