Need Function for Year-to-Date spent total

  • Thread starter Thread starter mokshadavid
  • Start date Start date
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
 
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
Click to expand...
Click to expand...
 
Back
Top