Posts by Maqbool

    Hi Bob:


    Please read the forum rules. You need to tag the code when you post.


    I am not sure i understand you fully.


    Try the following:




    otherwise post a sample workbook with expected result.



    Maqbool

    Hi Everyone:


    Hope Everyone doing well.


    Can someone help me on how to hide the running total of a field on a Pivot Table. See the attached spreadsheet. I need to hide the highlighted numbers until the months data available on table. Any help will be appreciated.


    Regards


    Maqbool

    Files

    • Test.xlsx

      (117.79 kB, downloaded 181 times, last: )

    Hi
    Can someone guide me how to dynamically change the source of a Power Query; My source files are in a folder. I am trying to change the source of the query by typing the file name in a cell. I found few videos which takes all files from the folder. But I need to use a single file as my data source. This source files are created on a weekly basis with a date suffix.
    Thank you very much
    Any help will be highly appreciated.

    Hi


    Try the code below:



    Regards


    Maqbool

    Hi


    Code below will solve both issues:


    Please leave blank row 1 on consolidation workbook.



    Regards


    Maqbool

    Hi


    Change the code as below:



    Regards


    Maqbool

    Hi


    Try the following. I have noticed an issues with your file extension (why you do have January 2010.xlsm.xlsx) . This needs to be corrected. Or change the file names on row 8.





    Regards


    Maqbool

    Hi


    I can't upload the workbook now. I will upload the workbook later today.


    Follow the below steps:


    Create three ranged names as below:


    Use below formula against each name


    [F]
    tblData: Sheet1!$C$1:INDEX(Sheet1!$C$1:$G$1000,COUNTA(Sheet1!$C:$C),5)


    rngCat : =INDEX(tblData,0,2)


    rngPercent =OFFSET(Sheet1!$C$1,MATCH(Sheet1!$J$1,rngCat,0)-1,4,COUNTIF(rngCat,Sheet1!$J$1))
    [/F]


    Then create the following formula on your sample sheet
    L21 - enter as an array formula (CTRL+SHIFT+Enter]


    [F]


    N21 = LARGE(rngPercent,$K21)


    L21 = OFFSET(rngPercent,MATCH(N21,rngPercent,0)-1,-4)


    M21= VLOOKUP(L21,tblData,MATCH($M$20,$C$1:$G$1,0),FALSE)


    [/F]



    Regards


    Maqbool

    Hi


    try the below :



    Regards


    Maqbool

    Hi


    The problems is below code:

    Code
    1. Range("C6:AD6").AutoFill Destination:=Range("C6:AD" & lastrow)


    This will copy down any thing you have on row 6 - eg. if on H6 - if you dont have a formula - and assume it is blank - so your h7 will blank, if you have 1 on H6 your H7 will be 1


    Regards


    Maqbool

    Hi


    Try this:



    Regards


    Maqbool

    Hi


    Sorry - I am not very clear about your requirement.


    Do you mean - Copy 16 rows ( ie 20% of 80 data points) to each sheet (say mar 01 to March 05). So each sheet will have 16 rows of data.


    Otherwise post expected result in each sheet (Mar 01, Mar 02 etc..) - Also, if you can input some meaningful data on the spreadsheet that will be more helpful to those who willing to help you.



    Regards



    Maqbool

    YouTube solution.


    The spread has lot of other info and the user needs to select respective fields and load the corresponding data.


    Regards


    Maqbool