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


"dallen917 via OfficeKB.com" <u51503@uwe wrote in message
news:954bfcb82662b@uwe...
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.)

T. Valko wrote:
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.

--
Message posted via http://www.officekb.com