Excel - Having problem with summary vs detail spreadsheet

[cohtrnr]

I am having the hardest time trying to figure out how to show my monthly numbers from a budget detail worksheet onto the budget summary worksheet. Here is a sample ofwhat my summary worksheet looks like:

CurMnth YTD Budget
Air Surv 10 245 816
Enf Cas 24 36 1550
Air Perm 26 170 1500

Here is a sample of my monthly detail:

AirSurv EnfCas AirPerm
JUL 235 12 144
AUG 10 24 26
SEP
OCT
NOV
DEC
TOTALS 245 36 170

How do I get each months to show under Current Month?

 

[SkipVought]

hi,

First, your dates, ought to be, well, er, uh, DATES! Strings like JAN, FEB, MAR are virtually WORTHLESS in a spreadsheet!

I'd use the FIRST of the month, enter the date like this
[tt]
jan 2010
[/tt]
The result is...
[tt]
1/1/2010
[/tt]
displayed as
[tt]
Jan-2010
[/tt]
Drag this cell down by the Fill Handle and and you will get the FIRST of each month.

Summary Table structure
[tt]
A1: =DATE(Year(Today()),1,1)
B1: =DATE(Year(Today()),Month(Today()),1)
B2: =SUMPRODUCT((INDIRECT($A2)*(MonDate=$B$1)))
C2: =SUMPRODUCT((INDIRECT($A2))*(MonDate<=$B$1)*(MonDate>=$A$1))
[/tt]
My results
[tt]
1/1/2010 Aug-10 YTD Budget
AirSurv 10 245
EnfCas 24 36
AirPerm 26 170
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cohtrnr]

I have 23 catagories for each month, how do I have this automatically fill in the current on the summary sheet,help me understand?

 

[SkipVought]

If you use Named Ranges, just list them all in column A of the summary and copy the formulas down.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cohtrnr]

I don't understand your results, you have the dates as headers and my headers are, CurMnth YTD Budget, so your references are confusing to me. I have a file sample.

Please help

 

[SkipVought]

Use whatever heading you want.

I have the first of the current year and the first of the current month in A1 & B1. So move them anywhere you want!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}