Create Dynamic chart based on data selected in drop down

     

    Create Dynamic chart based on data selected in drop down

    Features of this tempalte:

  • (i)It create names using offset function
  • (ii)Defines dynamic range using CountA function
  • (iii)Create Dynamic Data validations using Names
  • (iv)Allows the user to add more number of years\remove existing years. Updates data validation dynamically
  • (v)Create Chart based on Data selected in dropdown

    •  

    Download The Workbook

     

    Using Offset and CountA functions for Start year in Data validation:

    =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A$2:$A$100),1)

     

    Using Offset function to find the End Year:

    =OFFSET(Sheet2!$A$2,MATCH(Sheet2!$E$2,Sheet2!$A$2:$A$100,0)-1,0, COUNTA(Sheet2!$A$2:$A$100)-MATCH(Sheet2!$E$2,Sheet2!$A$2:$A$100,0)+1,1)

     

    Using Offset and Match functions for Year Data for chart:

    =OFFSET(Sheet2!$A$2,MATCH(Sheet2!$E$2,Sheet2!$A$2:$A$100,0)-1,0,MATCH(Sheet2!$I$2,Sheet2!$A$2:$A$100,0)- MATCH(Sheet2!$E$2,Sheet2!$A$2:$A$100,0)+1,1)

     

    Using Offset function to find the Sales Data:

    =OFFSET(Sheet2!Year_Data,0,1)