OFFSET function to pull the data of Previous Month to the Month selected

  • Hi,


    I have a dashboard in 'Tower Score Card' sheet and the subsequent Group and Month wise data back up is in the 'Statistics' Worksheet.


    In the Dashboard i have created two Drop Down Menus for selecting the Group and Month.


    My Requirement is,
    When i select the particular Group and Month from the Drop down i want to pull the data for that Group from the statistics worksheet and display that in the Column A of Tower Wise Scorecard Worksheet but
    ""The main thing here is that i want the previous month data to be displayed here.""


    For Example if i select 'Sample Group One' and Select the month 'November' the formula should pull the 'October' month data for the 'Sample Group One'.


    Please help me in finding out an OFFSET formula to perform this activity.


    Sample Attached. All the references are as per the original file.

    Files

  • Re: OFFSET function to pull the data of Previous Month to the Month selected


    Try:


    [COLOR="#0000FF"]=IFERROR(INDEX(Statistics!$C$4:$N$55,ROWS($A$11:$A11),MATCH($R$4,Statistics!$C$1:$N$1,0)-5+MATCH($N$4,Statistics!$C$2:$F$2,0)),"")[/COLOR]


    copied down.


    You will need to format the cells to match the original data.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Re: OFFSET function to pull the data of Previous Month to the Month selected


    Hi NVBC,


    Thanks It worked like a charm. Sorry for missing out a small point,


    October is the Start of the year so there will be no previous month, request you to please tweek the formula to display '0' when October month is selected through the drop down. From November onwards the formula should work.


    Sorry about that and request you to please modify.

  • Re: OFFSET function to pull the data of Previous Month to the Month selected


    Try:


    [COLOR="#0000FF"]=IF($R$4="October",0,IFERROR(INDEX(Statistics!$C$4:$N$55,ROWS($A$11:$A11),MATCH($R$4,Statistics!$C$1:$N$1,0)-5+MATCH($N$4,Statistics!$C$2:$F$2,0)),""))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Re: OFFSET function to pull the data of Previous Month to the Month selected


    Hi NBVC,


    In the sample file i have only 4 groups, but i have 10 files like that. In 1 dashboard there are only 4, but in the other there are 5, in other there are 13. How to find out a solution irrespective of the number of groups data is avaiable.

  • Re: OFFSET function to pull the data of Previous Month to the Month selected


    Assuming the groups always start at C2 of Statistics sheet, try:


    [COLOR="#0000FF"]=IF($R$4="October",0,IFERROR(INDEX(Statistics!$C$4:$N$55,ROWS($A$9:$A9),MATCH($R$4,Statistics!$C$1:$N$1,0)-MATCH(Statistics!$C$2,Statistics!$D$2:$ZZ$2,0)-1+MATCH($N$4,Statistics!$C$2:$F$2,0)),""))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016