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....
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
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....
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Book13.xlsx
thanks
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)))
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
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.
thanks Roger
Bookmarks