Dependent Drop Down List

49thParallel

New Member
Joined
Nov 30, 2017
Messages
2
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:

Month/YearLocationLocationLocationHigher or Lower then Location 1
November 20177.0%7.0%7.0%Higher
November 20167.0%7.0%7.0%Comparable
November 20157.0%7.0%7.0%Lower
November 20147.0%7.0%7.0%Significantly Higher

<tbody>
</tbody>









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!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

AFPathfinder

Well-known Member
Joined
Apr 8, 2012
Messages
519
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):
Code:
=DATE(YEAR(TODAY()),MONTH(TODAY())-ROW(A1)+1,1)
Format the column to match your style:
Code:
"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:
Code:
=A1
Cell A3 formula (fill handle drag to A5):
Code:
=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:
Code:
=MONTH(D2)
And another column to identify the year:
Code:
=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.
 
Last edited:

49thParallel

New Member
Joined
Nov 30, 2017
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top