+ Reply to Thread
Results 1 to 6 of 6

Create a list from a Table by selecting a year.

  1. #1
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Create a list from a Table by selecting a year.

    In the attached file I want to create a list by selecting a year from a dropdown list. The list will have between 8 and 12 names depending on the year.

    Just looking for a simple way to produce a variable range list.

    Jim O
    Attached Files Attached Files
    Last edited by JO505; 08-20-2013 at 08:45 PM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Create a list from a Table by selecting a year.

    hi Jim. maybe this array formula in H4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you might get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Create a list from a Table by selecting a year.

    1) Create a named range called YEARS of the cells C1:O1 on the List sheet.
    2) Use that named range in the Data Validation on List cell B3.

    3) The array formula in List cell D4 would be:

    =IFERROR(INDEX(Table2[Name], SMALL(IF(OFFSET(Table2[Name],,MATCH($B$2, Years, 0),,)<>"", ROW(Table2[Name])-1), ROWS($A$1:$A1))), "")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    4) When "Name3" appears in D4 successfully, copy D4 downward to get the rest of the list.
    Attached Files Attached Files
    Last edited by JBeaucaire; 08-19-2013 at 10:42 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Create a list from a Table by selecting a year.

    JBeaucaire,

    Thanks for your time. The sample is just what I was looking for, however I don't seem to understand it formula as well as I thought I did as witnessed in my attached file. I think my problem is in the "Rows" section of the formula.

    I can get the formula to display a list of names but not the correct ones. The attachment is a clearer example of my goal. I like the formula but I just cant quite get my brain around it fully.

    Thanks for the help.

    Jim O
    Attached Files Attached Files

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Create a list from a Table by selecting a year.

    You added more columns amidst the table between the names you want to return and the data table, 3 columns to be exact. So we have to adjust the OFFSET() part of the formula to move over those additional 3 columns.

    THe FIRST formula is:

    =IFERROR(INDEX(TeamUSATbl[USA Player], SMALL(IF(OFFSET(TeamUSATbl[USA Player],,MATCH($B$2, Year, 0)+3,,)<>"", ROW(TeamUSATbl[USA Player])-2), ROWS($A$1:$A1))), "")

  6. #6
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Create a list from a Table by selecting a year.

    JBeaucaire,

    Thanks for the help. It's working fine now. One of these days I'll figure this stuff out. In the mean time it's nice to have a place to go get answers.

    Thanks again for your time.

    Jim O

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create a selecting list
    By Rompetelo in forum Excel General
    Replies: 1
    Last Post: 06-19-2013, 09:57 AM
  2. [SOLVED] Selecting rows in a table based on another list
    By bedik in forum Excel General
    Replies: 9
    Last Post: 02-01-2013, 12:05 AM
  3. [SOLVED] how to return a day(number) by selecting month and year from data list
    By lizsantiago07 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-24-2012, 02:43 PM
  4. [SOLVED] How to create list on another tab by selecting qty from big list?
    By Rob in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-29-2005, 01:05 PM
  5. Create fiscal Year calendar list weeks by #1-52
    By akid12 in forum Excel General
    Replies: 0
    Last Post: 06-14-2005, 07:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1