Need Function for Year-to-Date spent total

M

mokshadavid

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("filename",A1),FIND("]",CELL("filename",A1))+1,256),6)),MONTH(LEFT(MID(CELL("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
 
B

Barb Reinhardt

What's a typical worksheet name?

Worksheet MMM YY or something to that effect?
 
M

mokshadavid

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

Thanks,
mokshadavid

Barb Reinhardt said:
What's a typical worksheet name?

Worksheet MMM YY or something to that effect?




mokshadavid said:
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("filename",A1),FIND("]",CELL("filename",A1))+1,256),6)),MONTH(LEFT(MID(CELL("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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top