AUTO POPULATING CELLS USING A DROP DOWN LIST

%3CLINGO-SUB%20id%3D%22lingo-sub-1850230%22%20slang%3D%22en-US%22%3EAUTO%20POPULATING%20CELLS%20USING%20A%20DROP%20DOWN%20LIST%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1850230%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EOn%20sheet%201%2C%20I%20have%20a%20column%20with%20all%20the%20months%20of%20the%20year.%20I%20made%20a%20dropdown%20list%20of%20those%20months.%20When%20I%20select%20from%20the%20drop%20down%20list%2C%20any%20month%2C%20I%20want%20to%20auto%20populate%20the%20prior%203%20months%20into%20another%20cell.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EExample%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EOn%20sheet%201%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EA8%3AA19%20are%20JAN%20through%20DEC.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ECell%20K1%20is%20the%20drop%20down%20list.%20When%20I%20select%20%22Jan%22%20from%20the%20drop%20down%20list(L1)%2C%20I%20want%20OCT%2C%20NOV%2C%20and%20DEC(Cells%20A17%3AA19)%20to%20auto%20populate%20to%20Sheet2!A8%3AA10.%20And%20if%20I%20select%20FEB%20from%20drop%20down%20list(K1)%20I%20want%20NOV%2C%20DEC%2C%20and%20JAN(A18%2CA19%2C%20and%20A8)%20to%20populate%20into%20Sheet2%20A8%3AA10%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20made%20it%20happen%20using%20INDEX%20and%20MATCH%2C%20making%20the%20cells(Sheet2!A8%2CA9%2CA10)%20reference%20the%20drop%20down%20list(sheet1!K1)%2C%20and%20looking%20up%20that%20value%20in%20the%20array%20I%20selected(sheet1!A8%3AA19)%2C%20but%20looking%20at%20the%20cell%20above%20that%20value%20once%20to%20populate%20sheet%202%20A10%2C%26nbsp%3B%20up%20twice%20for%20A9%2C%20and%20up%203%20times%20for%20A8.%20However%2C%20that%20only%20works%20for%20APR-DEC.%20For%20example.%20When%20I%20select%20JAN%2C%20the%20value%20I%20want%20it%20to%20return%20is%20OCT%2C%20NOV%2C%20and%20DEC.%20Well%2C%20when%20the%20cell%20looks%20up%201%2C%202%2C%20and%203%20times%20from%20JAN%2C%20I%20need%20it%20to%20basically%20rotate%20to%20the%20bottom%20of%20the%20chart%2C%20which%20is%20where%20OCT%2C%20NOV%2C%20and%20DEC%20are.%20However%2C%20with%20that%20formula%2C%20the%20value%20it%20is%20looking%20for%20does%20not%20exist%2C%20since%20technically%20JAN%20is%20the%20top%20of%20the%20array%2C%20so%20when%20it%20ties%20to%20take%20a%20look%20up%201%20cell%2C%20there's%20nothing%20there.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20know%20that%20is%20all%20probably%20very%20confusing.%20And%20if%20anyone%20is%20thinking%20it's%20not%20possible%2C%20it%20is.%20At%20my%20work%2C%20we%20have%20a%20workbook%20that%20does%20exactly%20what%20I'm%20talking%20about.%20We%20still%20use%20it%2C%20but%20we%20also%20added%20another%20type%20of%20worker.%20We%20need%20to%20recreate%20the%20old%20workbook%2C%20but%20just%20edit%20some%20of%20the%20field%20descriptions.%20However%2C%20that%20old%20workbook%20is%20protected%20and%20none%20of%20use%20know%20the%20password%2C%20and%20therefore%20cannot%20see%20the%20formulas%20used%2C%20and%20cannot%20edit%20any%20of%20the%20fields%20I%20was%20talking%20about.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20two%20files.%20The%20DIVISION%203%20PATE%20is%20the%20old%20workbook.%20The%20LEADS%20PATE%20is%20the%20new%20one%20that%20I%20started%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1850230%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1850651%22%20slang%3D%22en-US%22%3ERe%3A%20AUTO%20POPULATING%20CELLS%20USING%20A%20DROP%20DOWN%20LIST%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1850651%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F856260%22%20target%3D%22_blank%22%3E%40Joseph_Longoria%3C%2FA%3E%26nbsp%3B%2C%20pls%20try%20these%203%20formulas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2264%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2220%22%3E%3DINDEX(%24A%248%3A%24A%2419%2CIF(MOD(MATCH(%24K%241%2C%24A%248%3A%24A%2419%2C0)%3CFONT%20color%3D%22%23FF0000%22%3E-3%3C%2FFONT%3E%2C12)%3D0%2C12%2CMOD(MATCH(%24K%241%2C%24A%248%3A%24A%2419%2C0)%3CFONT%20color%3D%22%23FF0000%22%3E-3%3C%2FFONT%3E%2C12)))%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E%3DINDEX(%24A%248%3A%24A%2419%2CIF(MOD(MATCH(%24K%241%2C%24A%248%3A%24A%2419%2C0)%3CFONT%20color%3D%22%23FF0000%22%3E-2%3C%2FFONT%3E%2C12)%3D0%2C12%2CMOD(MATCH(%24K%241%2C%24A%248%3A%24A%2419%2C0)%3CFONT%20color%3D%22%23FF0000%22%3E-2%3C%2FFONT%3E%2C12)))%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E%3DINDEX(%24A%248%3A%24A%2419%2CIF(MOD(MATCH(%24K%241%2C%24A%248%3A%24A%2419%2C0)%3CFONT%20color%3D%22%23FF0000%22%3E-1%3C%2FFONT%3E%2C12)%3D0%2C12%2CMOD(MATCH(%24K%241%2C%24A%248%3A%24A%2419%2C0)%3CFONT%20color%3D%22%23FF0000%22%3E-1%3C%2FFONT%3E%2C12)))%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1850654%22%20slang%3D%22en-US%22%3ERe%3A%20AUTO%20POPULATING%20CELLS%20USING%20A%20DROP%20DOWN%20LIST%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1850654%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F856260%22%20target%3D%22_blank%22%3E%40Joseph_Longoria%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20an%20intriguing%20problem%2C%20and%20very%20solvable.%20You%20sound%20like%20you're%20very%20competent%2C%20so%20I'm%20just%20going%20to%20point%20you%20in%20a%20different%20direction%20and%20challenge%20you%20to%20resolve%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20my%20advice%3A%20stop%20thinking%20of%20backing%20up%20the%20list%20(the%20list%20that%20names%20the%20months)%20three%20months.%20Start%20thinking%20just%20of%20numbers%2C%20specifically%20numbers%201%20through%2012.%20And%20then%20investigate%20the%20MOD%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou'll%20find%20that%20If%20you%20enter%20any%20number%201%20through%2012%20in%20cell%20A1%2C%20this%20function%20will%20yield%20the%20number%20of%20the%20month%203%20months%20prior.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DMOD(A1-3%2C12)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EFrom%20that%2C%20it%20should%20be%20child's%20play%20to%20convert%20that%20starting%20number%20to%20the%20numbers%20(and%20then%20the%20names)%20of%20the%20three%20prior%20months.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1850675%22%20slang%3D%22en-US%22%3ERe%3A%20AUTO%20POPULATING%20CELLS%20USING%20A%20DROP%20DOWN%20LIST%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1850675%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20my%20given%203%20formulas%2C%20refer%20to%20Sheet1%20as%20necessary.%20e.g.%20instead%20of%20K1%2C%20write%20Sheet1!K1%20as%20you%20already%20did%20in%20your%20drafted%20formulas%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1850705%22%20slang%3D%22en-US%22%3ERe%3A%20AUTO%20POPULATING%20CELLS%20USING%20A%20DROP%20DOWN%20LIST%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1850705%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20for%20calling%20me%20competent.%20In%20all%20honest%20though%2C%20a%20week%20ago%2C%20all%20I%20knew%20how%20to%20do%20was%20%3DSUM%20haha.%20I%20just%20dove%20deep%20into%20research%20and%20figuring%20things%20out(Thank%20God%20for%20YouTube%20and%20the%20internet)%20I%20don't%20know%20anything%20about%20the%20MOD%20function%2C%20but%20just%20like%20with%20the%20INDEX%20and%20MATCH%20functions%2C%20I%20will%20do%20my%20research%20and%20figure%20it%20out.%20Thank%20you%20for%20the%20help.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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
Highlighted

@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)))
Highlighted

@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.

Highlighted

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

Highlighted
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.
Highlighted
Hey @Joseph_Longoria, psst.. the solution to the quiz is in my 1st reply just in case but like @mathetes I would also be happy if you would work it out yourself..
BTW, Mr. @mathetes user name is also very intriguing.. it sounds like "math it is".. and probably that's why a nice way of throwing up a math challenge
Highlighted
You're amazing!!! fixed it Thank you so much!
Highlighted

@Joseph_Longoria 

As variant

image.png

in A8 it could be

=UPPER(TEXT(DATE(Sheet1!$H$1,MONTH(1&Sheet1!$S$1)+8+ROW()-ROW($A$7),1),"mmm"))

and drag it down to next two cells

Highlighted

@amit_bhola 

 

BTW, Mr. @mathetes user name is also very intriguing.. it sounds like "math it is".. and probably that's why a nice way of throwing up a math challenge

 

Mathetes is actually a Greek word that means learner or student. I like to employ it as my user name on sites like this, where I find I learn a lot over time. And you touched on something: a quick Google search tells me that there is a connection with math as well; I hadn't known that.

 
 

https://www.google.com/search?hl=en&sxsrf=ALeKk03d_qPr0cyWlecuj4JQZ-wb3p0nCQ%3A1604439038602&source=...