Calculate based on Fiscal Year

[Divinedar]

The attached excel worksheet can explain better of what I need. I need to calculate the attached based on fiscal year starting with August and ending in July for the next 5 years. If you have any other questions for better understanding please let me know.

[Divinedar]

I'm not sure if the attachment ability of these discussions work but I notice there is no attachment with my question. So I'm going to post what I put in the attachment and I pray that someone understands:

Our fiscal year is from months August to July. So if I put an 8 in F22 it will calculate the first year column AC for F22 to Q22. If I enter 10 in F22 then months 10 through 19 would calculate year 1 because year one would be 8 through 19 and months 20 to 31 would be based on year 2, etc... This code is located in F39 to Q39: =SUMPRODUCT(F23:F38*OFFSET($AC$5,0,INT(F22/12)):OFFSET($AC$20,0,INT(F22/12))) which works perfectly for the 12 months, 1-12. So I need for it to calculate by fiscal year for the next five years per the table above, i.e., Aug 2012 to July 2013, year 1; Aug 2013 to July 2014, year 2; Aug 2014 to July 2015, year 3; August 2015 to July 2016, year 4; and August 2016 to July 2017, year 5.

[Divinedar]

Can anybody respond. Anybody have any ideas I can at least start with.

[AndreRet]

Divine, I think we are confused with the exact sums involved. I'm quite good at excel macros, even I'm stumped. ;) Try and attach your worksheet and I will play with it. The only way I'll be able to help you get a solution.... I need to see what the sheets are doing and why to grasp your problem... :)

[Divinedar]

Thank you AndreRet. See the attached and I think it explains more.

[Divinedar]

Let me know if you get the attachment because I don't see it attached.

[AndreRet]

Nope, still not attached. :)

If you click on "Files" it should show you the upload options... Not sure why yours is not working. I;ll flag a moderator and let them check.

[Divinedar]

That's what I did but I'm trying again. I'm getting this error: The filetype you are attempting to upload is not allowed. It's an .xlsm file.

[AndreRet]

Ah, thats why. Can you take a screen shot and post the image maybe? that will also help..

[AndreRet]

or you can save it as a .xls file, select convert/save as excel 2003.

[Divinedar]

There seems to be a button that says upload attachments but it's faded out. I did save it as a 2003 file but it still seems to not work. I've attached it to here so let me know if you receive it.

[AndreRet]

Please check your pm. I'll post the solution here when done...

[Divinedar]

Thank you. I'm good at challenging folks but I like a challenges myself.

[AndreRet]

Got your mail. Will post as soon as I have a solution. :)

[Divinedar]

The time here is 2 PM. I leave at 4 PM. So take your time.

[AndreRet]

Hahaha, its now 21:00 by me. almost done for today :)

[Divinedar]

You must work at night cause that 9pm my time. So what day is it there am I 6 hours ahead of you or 6 hours behind you. Today is Wed here.

[AndreRet]

I think 6 hours behind :)

[peter_budo]

Compress document into ZIP file and attach that one