YTD Calculation using SUM and OFFSET

[dallen917]

I have some financial data with months (Jan - Dec) across the top and cost
categories (such as Labor, Materials, Travel) down the side. This data is
replicated in a large number of identical worksheets (Dept A, Dept B, Dept C.,
etc).

I have a summary sheet up front and I want to do a Year to Date calculation
which for each cost category based on the current month. I have a cell in
which the current month is entered. I found a formula for YTD calculations
which works well for one worksheet:

"=SUM(OFFSET(C2,0,0,1,A1))" where A1 is the current month and the month by
month data is in C2 through n2.

How can I adapt this to work with multiple worksheets? I tried "=SUM
(Worksheet1:Worksheet25!OFFSET(C2,0,0,1,A1))" but it errored.

 

[T. Valko]

If you want a YTD total then only the past months (and maybe the current
month) should have data, right?

identical worksheets (Dept A, Dept B, Dept C.,
Worksheet1:Worksheet25

Ok, so what are the REAL sheet names? If the REAL sheet names follow a
sequential naming pattern like Dept A, Dept B, Dept C then that makes things
a little bit easier.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Consolidate multiple sheets.
Year To Date is a built-in feature of 2007.
Work saving macro included.
http://www.mediafire.com/file/wgmjyyuijnu/04_28_09.xlsm

 

[dallen917 via OfficeKB.com]

Actually, there is full year budget data in the sheets. I want to be able to
pull YTD budget based on the current month. The tabs will all be in order by
cost center number (30100, 30101, 30102, etc.)

If you want a YTD total then only the past months (and maybe the current
month) should have data, right?

identical worksheets (Dept A, Dept B, Dept C.,
Worksheet1:Worksheet25

Ok, so what are the REAL sheet names? If the REAL sheet names follow a
sequential naming pattern like Dept A, Dept B, Dept C then that makes things
a little bit easier.

I have some financial data with months (Jan - Dec) across the top and cost
categories (such as Labor, Materials, Travel) down the side. This data is

[quoted text clipped - 14 lines]

How can I adapt this to work with multiple worksheets? I tried "=SUM
(Worksheet1:Worksheet25!OFFSET(C2,0,0,1,A1))" but it errored.

 

[T. Valko]

This is a bit more complicated than you might think because excel makes 3D
referencing difficult. Very few functions support 3D references and those
that do are for very basic calculations. This is slightly more than a basic
calculation.

With that in mind I put together a small sample file that demonstrates this.
I'm pretty sure that you'll have a hard time understanding the formula used
for this and when you try to apply it to your data it probably won't work!
I'm including the sample file so you can see that it does in fact work.

Ok, I'm working on these assumptions:

The sheet names are a sequential number string that starts with 30100 to
30nnn.
Every sheet is structured *exactly* the same.
B1:M1 are the month names as column headers
A2:An are categories
The categories are on the same row in every sheet.
On the summary sheet you enter a date in cell A1. You want the YTD totals
from January to the month of the date in cell A1.
On the summary sheet the categories are listed starting in cell A3.

This formula entered on the summary sheet in cell B3 will return the YTD
total for the category in cell A3 from the sheets named 30100 and 30101:

=SUMPRODUCT(SUMIF(INDIRECT("'30"&ROW(INDIRECT("100:101"))&"'!R"&MATCH(A3,'30100'!A:A,0)&"C2:R"&MATCH(A3,'30100'!A:A,0)&"C"&MONTH(A$1)+1,0),"<1E100"))

Copy down as needed.

If you want to use this on sheets 30100 to 30125 change this portion of the
formula:

INDIRECT("100:101")

The syntax is:

INDIRECT("first_sheet:last_sheet")

INDIRECT("100:125")

If you want to take the easy route on this you could use a helper column on
each sheet and then use a *simple* 3D sum formula. I've included this in the
sample file.

Here's the link to the sample file:

xDallen.xls 16kb

http://cjoint.com/?eEty16FGrJ


--
Biff
Microsoft Excel MVP

Actually, there is full year budget data in the sheets. I want to be able
to
pull YTD budget based on the current month. The tabs will all be in order
by
cost center number (30100, 30101, 30102, etc.)

If you want a YTD total then only the past months (and maybe the current
month) should have data, right?

identical worksheets (Dept A, Dept B, Dept C.,
Worksheet1:Worksheet25

Ok, so what are the REAL sheet names? If the REAL sheet names follow a
sequential naming pattern like Dept A, Dept B, Dept C then that makes
things
a little bit easier.

I have some financial data with months (Jan - Dec) across the top and
cost
categories (such as Labor, Materials, Travel) down the side. This data
is

[quoted text clipped - 14 lines]

How can I adapt this to work with multiple worksheets? I tried "=SUM
(Worksheet1:Worksheet25!OFFSET(C2,0,0,1,A1))" but it errored.