Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Dependent Drop Down List

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Dependent Drop Down List

    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/Year Location Location Location Higher or Lower then Location 1
    November 2017 7.0% 7.0% 7.0% Higher
    November 2016 7.0% 7.0% 7.0% Comparable
    November 2015 7.0% 7.0% 7.0% Lower
    November 2014 7.0% 7.0% 7.0% Significantly Higher









    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!

  2. #2
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    519
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dependent Drop Down List

    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 by AFPathfinder; Dec 1st, 2017 at 08:10 AM.

  3. #3
    New Member
    Join Date
    Nov 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dependent Drop Down List

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •