Formula to extract data based on Month & Year

    I have source data on Sheet1 for Jul 2017 (cell K1) on a particular workbook which is imported-see sample Data Below



    I have July 2017 on another Workbook (K4) and would like to extract the figures for each dept pertaining to the month Applicable



    I would like a formula to extract the data for the applicable month in J1 for e.g. data in row 2 applicable for Jul 2017 which in this example is-56,986, row3 which is -3,606 etc



    It would be appreciated if someone can assist me


    I have also posted on Mr Excel.com


    https://www.mrexcel.com/forum/…ata-based-month-year.html

    Re: Formula to extract data based on Month & Year


    Hello,


    There are several steps required to build the formula you need ...


    The first deals with the fact you are using a date (which is a number ...) in Profits by Months ... and the Branch worksheet is using actual text ...


    So in cell K1, you need following transformation formula ...


    Code
    =TEXT(DATE(1,MONTH(K4),1),"mmm")&" "&YEAR(K4)


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Formula to extract data based on Month & Year


    Hello again,


    Once you have made the transformation, then you can move on to the next two steps ...


    add a match formula in cell K2


    Code
    =MATCH("*"&K$1&"*",'[Branch Profits.xlsx]Sheet1'!$1:$1,0)


    and in cell K5, add your offset formula :


    Code
    =OFFSET('[Branch Profits.xlsx]Sheet1'!$A$1,ROW()-4,K$2)


    Hope this will help

    Re: Formula to extract data based on Month & Year


    You are welcome ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login