Nov 03 2020 09:37 AM
Nov 03 2020 09:37 AM
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.
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
Nov 03 2020 11:21 AM
@Joseph_Longoria , pls try these 3 formulas
Nov 03 2020 11:23 AM
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.
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.
Nov 03 2020 11:25 AM - edited Nov 03 2020 11:25 AM
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
Nov 03 2020 11:35 AM
Nov 03 2020 11:58 AM
Nov 03 2020 01:35 PM
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.