Forum Discussion

Jarvo's avatar
Jarvo
Copper Contributor
Mar 16, 2021

How to change the dates according to a year selected in a drop down list

Hi everyone!

 

I've been scratching my head and I'm sure there is a simple solution. On the spreadsheet you'll see a drop down list at the top where you can select the current year and subsequent years up to 2032. On the table below it, there are some columns on the right with the month and year going from March to July 2021. Is there a way I can get these cells to automatically update what the year is while keeping the months the same, based on the year selected in the drop down menu? I hope that makes sense!

 

Thanks for any help you can offer

BI & Data Analysis
excel
Formulas and Functions

5 Replies

    • Jarvo's avatar
      Jarvo
      Copper Contributor
      Mar 16, 2021
      Thank you I had not realised that!
  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Mar 16, 2021

    Hi Jarvo 

     

    You may use below formula :

    ="Mar-"&RIGHT($E$2,2) so it will be changed when year will be changed.

    This formula will work in regular cell but I doubt it will work if cells are Hearders of a table (as in your case).

     

    A sample file is also attached for your reference. Please let me know if it works for you.

     

    Thanks

    Tauqeer

    • Jarvo's avatar
      Jarvo
      Copper Contributor
      Mar 16, 2021

      tauqeeracma 

       

      Thanks! Yeah as was suggested in the other response, the table headers don't allow for functions/formulas (which I hadn't realised). So I've done an alternative for table headers and your formula  works! Thank you very much you've helped me a lot here.

Resources