Forum Discussion

Joseph_Longoria's avatar
Joseph_Longoria
Copper Contributor
Nov 03, 2020

AUTO POPULATING CELLS USING A DROP DOWN LIST

On sheet 1, I have a column with all the months of the year. I made a dropdown list of those months. When I select from the drop down list, any month, I want to auto populate the prior 3 months into another cell.
Example:
On sheet 1,
A8:A19 are JAN through DEC.
Cell K1 is the drop down list. When I select "Jan" from the drop down list(L1), I want OCT, NOV, and DEC(Cells A17:A19) to auto populate to Sheet2!A8:A10. And if I select FEB from drop down list(K1) I want NOV, DEC, and JAN(A18,A19, and A8) to populate into Sheet2 A8:A10

I made it happen using INDEX and MATCH, making the cells(Sheet2!A8,A9,A10) reference the drop down list(sheet1!K1), and looking up that value in the array I selected(sheet1!A8:A19), but looking at the cell above that value once to populate sheet 2 A10,  up twice for A9, and up 3 times for A8. However, that only works for APR-DEC. For example. When I select JAN, the value I want it to return is OCT, NOV, and DEC. Well, when the cell looks up 1, 2, and 3 times from JAN, I need it to basically rotate to the bottom of the chart, which is where OCT, NOV, and DEC are. However, with that formula, the value it is looking for does not exist, since technically JAN is the top of the array, so when it ties to take a look up 1 cell, there's nothing there.

I know that is all probably very confusing. And if anyone is thinking it's not possible, it is. At my work, we have a workbook that does exactly what I'm talking about. We still use it, but we also added another type of worker. We need to recreate the old workbook, but just edit some of the field descriptions. However, that old workbook is protected and none of use know the password, and therefore cannot see the formulas used, and cannot edit any of the fields I was talking about. 

 

I have attached two files. The DIVISION 3 PATE is the old workbook. The LEADS PATE is the new one that I started

8 Replies

  • mathetes's avatar
    mathetes
    Gold Contributor

    Joseph_Longoria 

     

    It's an intriguing problem, and very solvable. You sound like you're very competent, so I'm just going to point you in a different direction and challenge you to resolve it.

     

    Here's my advice: stop thinking of backing up the list (the list that names the months) three months. Start thinking just of numbers, specifically numbers 1 through 12. And then investigate the MOD function.

     

    You'll find that If you enter any number 1 through 12 in cell A1, this function will yield the number of the month 3 months prior.

    =MOD(A1-3,12)

    From that, it should be child's play to convert that starting number to the numbers (and then the names) of the three prior months.

    • Joseph_Longoria's avatar
      Joseph_Longoria
      Copper Contributor
      Thank you so much for calling me competent. In all honest though, a week ago, all I knew how to do was =SUM haha. I just dove deep into research and figuring things out(Thank God for YouTube and the internet) I don't know anything about the MOD function, but just like with the INDEX and MATCH functions, I will do my research and figure it out. Thank you for the help.
  • amit_bhola's avatar
    amit_bhola
    Iron Contributor

    Joseph_Longoria , pls try these 3 formulas

     

    =INDEX($A$8:$A$19,IF(MOD(MATCH($K$1,$A$8:$A$19,0)-3,12)=0,12,MOD(MATCH($K$1,$A$8:$A$19,0)-3,12)))
    =INDEX($A$8:$A$19,IF(MOD(MATCH($K$1,$A$8:$A$19,0)-2,12)=0,12,MOD(MATCH($K$1,$A$8:$A$19,0)-2,12)))
    =INDEX($A$8:$A$19,IF(MOD(MATCH($K$1,$A$8:$A$19,0)-1,12)=0,12,MOD(MATCH($K$1,$A$8:$A$19,0)-1,12)))
    • amit_bhola's avatar
      amit_bhola
      Iron Contributor

      In my given 3 formulas, refer to Sheet1 as necessary. e.g. instead of K1, write Sheet1!K1 as you already did in your drafted formulas

Resources