need a macro

[applebee]

Helo Gurus


please let me know how to write a macro for the below

there is a report where i have MTD,QTD,YTD,FY tabs for all the 7 products with Actual,plan and Budget versions
all the tabs must expand based onthe selection of the month in the MTD tab which I have accomplished
MTD is fine, when in QTD i want to get the month data for all the three versions ( i know i can get this by hidden sheets with the expansion on all the three versions but this is going to be 7 * 3 which is 21 with all the 12 months for each i.e this is going to be a performance problem and an issue with excel being slow, since already there are 4 Tabs)
the issue with QTD is , since our year starts in april and the quarter is aril may and june , and if the user selects may in MTD the report must drive may to QTD and YTD respectively
when may in QTD the accumulation must happen for april and may
if YTD also goes the same

thanks

[Ken Puls]

Hi Applebee,

Can you upload a stripped down sample that shows what you're after? Would be a little easier for us than trying to mock up your data....

[applebee]

Hi Applebee,

Can you upload a stripped down sample that shows what you're after? Would be a little easier for us than trying to mock up your data....

Book13.xlsx


thanks

[Roger Govier]

Hi

take a look at the attached workbook.

I changed your data in cells F26:Q26 to make the values true Excel dates for the end of each month, but gave them a Custom Formant of yyyy.mmm so they look like you had them before 2010.Apr etc.

I created a lookup table called Qtrs in cells S25:Q36 to be able to provide the start month for each quarter, and what the Months relative position is in your data ( i.e. it is offset by 3 months as your first month is April)

I also created a named range called Months for cells F26:Q26 and a range called Data for cells F27:Q27

I them gave cell E33 a Data Validation dropdown with List=Months, and then the formula in cell E34 is simply
=SUM(INDEX(Data,VLOOKUP(MONTH(E33),Qtr,2)):INDEX(Data,VLOOKUP(MONTH(E33),Qtr,3)))

[applebee]

that was awesome to determine the months in the Quarter, but for this to happen i need to download all the data that is in the data base for all the versions
if the version Actual Budget, Forecast also can be retrieved automated by the kind of lookup You have shown that would be awesome.
Could u provide help onthat too, so i can avoid downloading the data everytime i run the report?

i mean when i select may month, i should be able to select actual, plan and forecast versions in three seperate columns

say may may may
actuals plan forecast

thanks i learnt something today with your help

[Roger Govier]

Hi

Yes, of course the same principle can be used to pull in Actuals, Plan and Forecast, provided you set up named ranges for Plan and Forecast.

I'm not sure what you mean by downloading all of the data each time.
Maybe we should take this offline, and you send directly to me your actual workbook and a full description of what you are trying to achieve.
To mail me direct
roger at technology4u dot co dot uk

Replace the at and dots with the usual characters to make a valid email address.

[applebee]

thanks Roger