Need Function for Year-to-Date spent total
 

I'm doing a Budget Worksheet for the Fiscal Year 08-09. I am looking for a
function that takes the previous month worksheet's "YTD spent" total, adds it
to the current month's "Monthly Spent" total to come up with that month's
"YTD spent" total to display in the current month's worksheet.

The person who set up this budget sheet used the Function below. However,
it doesn't work for a Fiscal Year, because (for example) in Jan. 08, the
function below starts trying to reference the current date, and tries to call
in Dec. 08 instead of Dec. 07 numbers. Otherwise, it works fine for half the
Fiscal year, but I'd like to get past having to manually fix it. Maybe there
is a small fix that someone can help me with.

=INDIRECT("'"&TEXT(DATE(YEAR(LEFT(MID(CELL("filena me",A1),FIND("]",CELL("filename",A1))+1,256),6)),MONTH(LEFT(MID(C ELL("filename",A1),FIND("]",CELL("filename",A1))+1,256),6))-1,1),"mmm
yy")&"'!"&CELL("address",E76)) +E75

Where, E76 = Year-to-Date spent, and E75 = current Month spending. Most of
the function is to reference the previous month's worksheet, then adds the
numbers.

Anybody got any good ideas? Everybody here is so great!
Thanks,
mokshadavid

Need Function for Year-to-Date spent total
 

What's a typical worksheet name?

Worksheet MMM YY or something to that effect?




"mokshadavid" wrote:

I'm doing a Budget Worksheet for the Fiscal Year 08-09. I am looking for a
function that takes the previous month worksheet's "YTD spent" total, adds it
to the current month's "Monthly Spent" total to come up with that month's
"YTD spent" total to display in the current month's worksheet.

The person who set up this budget sheet used the Function below. However,
it doesn't work for a Fiscal Year, because (for example) in Jan. 08, the
function below starts trying to reference the current date, and tries to call
in Dec. 08 instead of Dec. 07 numbers. Otherwise, it works fine for half the
Fiscal year, but I'd like to get past having to manually fix it. Maybe there
is a small fix that someone can help me with.

=INDIRECT("'"&TEXT(DATE(YEAR(LEFT(MID(CELL("filena me",A1),FIND("]",CELL("filename",A1))+1,256),6)),MONTH(LEFT(MID(C ELL("filename",A1),FIND("]",CELL("filename",A1))+1,256),6))-1,1),"mmm
yy")&"'!"&CELL("address",E76)) +E75

Where, E76 = Year-to-Date spent, and E75 = current Month spending. Most of
the function is to reference the previous month's worksheet, then adds the
numbers.

Anybody got any good ideas? Everybody here is so great!
Thanks,
mokshadavid

Need Function for Year-to-Date spent total
 

Yes, a typical worksheet name is like Jun 08 or Aug 08.

Thanks for the reply.

"Barb Reinhardt" wrote:

What's a typical worksheet name?

Worksheet MMM YY or something to that effect?

Need Function for Year-to-Date spent total
 

Is there any more information that I can provide that may be of help?

Thanks,
mokshadavid

"Barb Reinhardt" wrote:

What's a typical worksheet name?

Worksheet MMM YY or something to that effect?




"mokshadavid" wrote:

I'm doing a Budget Worksheet for the Fiscal Year 08-09. I am looking for a
function that takes the previous month worksheet's "YTD spent" total, adds it
to the current month's "Monthly Spent" total to come up with that month's
"YTD spent" total to display in the current month's worksheet.

The person who set up this budget sheet used the Function below. However,
it doesn't work for a Fiscal Year, because (for example) in Jan. 08, the
function below starts trying to reference the current date, and tries to call
in Dec. 08 instead of Dec. 07 numbers. Otherwise, it works fine for half the
Fiscal year, but I'd like to get past having to manually fix it. Maybe there
is a small fix that someone can help me with.

=INDIRECT("'"&TEXT(DATE(YEAR(LEFT(MID(CELL("filena me",A1),FIND("]",CELL("filename",A1))+1,256),6)),MONTH(LEFT(MID(C ELL("filename",A1),FIND("]",CELL("filename",A1))+1,256),6))-1,1),"mmm
yy")&"'!"&CELL("address",E76)) +E75

Where, E76 = Year-to-Date spent, and E75 = current Month spending. Most of
the function is to reference the previous month's worksheet, then adds the
numbers.

Anybody got any good ideas? Everybody here is so great!
Thanks,
mokshadavid