Year To Date Ideas

[Steven]

I have monthly reports that track employee expenses. Each
month is a seperate workbook. Management has decided they
also want a year to date sheet. My question is this: Do I
now need to combine all four worksheets to make this work?
Or is there a way to do have a seperate workbook for YTD
and each month. If I can keep them seperate, is there a
macro to write so that each month I don't have to change
the formula on the YTD workbook? I would try to get more
specific, but I honestly don't really know what I'm
looking for.

Thanks Everyone,
Steve

 

[Paul Corrado]

You can spend a few hours/days (depending on your VB programming skills)
building a YTD worksheet with the automated capability you are seeking and
this would get the job done.

However, that approach is infinitely more complicated than is necessary.

The more simple method is to combine the data into one worksheet in a table
format and then use SUMPRODUCT formulae to generate monthly and YTD results
as required.

PC

 

[Richard O. Neville]

Are you mixing up workbooks and worksheets? A workbook is a document, while
a worksheet is a sheet tab within a workbook. I think you might have one
workbook per employee, and perhaps 12 worksheets within it for each month's
expenses. If so, summing the YTD is easy.

Create a 13th worksheet and name it YTD. In the appropriate cell, create a
formula which sums a certain cell on each of the "month" worksheets; let's
say sheet 1 is January, sheet 2 February, etc. To create the formula, type =
and then go to sheet 1 and click on the total; type +, go to sheet 2 and
click on the total, etc. As of today your YTD would be only through most of
September, but do include the last three months in your formula. Those
totals will be 0 until something is added to the last three worksheets, and
you won't have to change the formula each month.