Results 1 to 7 of 7

Thread: need a macro

  1. #1

    need a macro



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    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 (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  3. #3

    need a macro

    Quote Originally Posted by Ken Puls View Post
    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

  4. #4
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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)))
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  5. #5

    need macro

    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

  6. #6
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  7. #7
    thanks Roger

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •