Dependent Drop Down List

[49thParallel]

Hi Everyone,

So I have been having an issue with figuring out the best way to display some data after some user input through a drop down list. To give you an idea of what I want I currently have in mind as an end result, this is what I would like it to look like in a word document or excel table for easy copy and pasting of values into word:

[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD]Month/Year[/TD]
[TD]Location[/TD]
[TD]Location[/TD]
[TD]Location[/TD]
[TD]Higher or Lower then Location 1[/TD]
[/TR]
[TR]
[TD]November 2017[/TD]
[TD]7.0%[/TD]
[TD]7.0%[/TD]
[TD]7.0%[/TD]
[TD]Higher[/TD]
[/TR]
[TR]
[TD]November 2016[/TD]
[TD]7.0%[/TD]
[TD]7.0%[/TD]
[TD]7.0%[/TD]
[TD]Comparable[/TD]
[/TR]
[TR]
[TD]November 2015[/TD]
[TD]7.0%[/TD]
[TD]7.0%[/TD]
[TD]7.0%[/TD]
[TD]Lower[/TD]
[/TR]
[TR]
[TD]November 2014[/TD]
[TD]7.0%[/TD]
[TD]7.0%[/TD]
[TD]7.0%[/TD]
[TD]Significantly Higher[/TD]
[/TR]
</tbody>[/TABLE]









What I want the user to do is select the most recent date (or any date) and have it display that date and the previous 3 years afterwards in the rows beneath. From there I want the user to select a location (City, State, Country) for the next three columns, each displaying the data that corresponds to the location and the month/year. The last column would just be an IF statement that I don't need help with but I have never used dropdown lists that pull data from specific dates.

Just looking for a slight nudge to get me going in the right direction as I've enjoyed researching but have hit a brick wall.

Thanks in advance!

 

[AFPathfinder]

I'd suggest having a dedicated column on a separate sheet or hidden column that has 12 dates for the prior 12 months for the drop-down. You could even setup the column with formulas to dynamically update based on today's date and setup the cell format to match.
Cell A2 formula (use fill handle to drag down to A13):

=DATE(YEAR(TODAY()),MONTH(TODAY())-ROW(A1)+1,1)

Format the column to match your style:

"mmmm yyyy"

---

---

You can then link your validation list drop-down to A2:A13. Since you are actually using a real date, you can use all the other date functions to move around. From the drop-down menu, the formulas in your summary table can adjust the year.
Cell A2 formula:

=A1

Cell A3 formula (fill handle drag to A5):

=DATE(YEAR(A2)-1,MONTH(A2),1)

---

---

Now to pull the rest of the data, add a column to your data table to identify the month of the transaction date:

=MONTH(D2)

And another column to identify the year:

=YEAR(D2)

Now you can use SUMIFS and such to summarize the date ranges. At least this is how I'd try to solve that issue.

 

[49thParallel]

Thank you for the reply, I managed to get the date part sorted by using EOMONTH and having it roll back 1 year on the current month/year. Just trying to figure out how now to setup another drop down for the locations to look at the dates and pull the data for those specific months/years and for that location.

I appreciate the tips though, I've enjoyed teaching myself the vlookups.