+ Reply to Thread
Results 1 to 9 of 9

Name Range + Drop down list to crossmatch.

  1. #1
    Registered User
    Join Date
    08-07-2019
    Location
    Sydney
    MS-Off Ver
    365 pro plus
    Posts
    17

    Name Range + Drop down list to crossmatch.

    Hello all, last August i got some great help with a spreadsheet i was doing for my cricket association. Further to the sheet I'd like to improve on it and have come across a minor problem.

    Now the Name range for the Green and Orange highlighted cells well on there own, reading the name, no problem.

    My question is, is that I'd like to select the 'Season' drop down first, then the 'Grade' drop down second where the possible cells are narrowed down.

    You will notice that Peter has 2 rows of data for 2018/19 season and 1 for 2019/20. I would like to select Cell P10 and select "Season" 2019/20 and only have one possible drop down in cell O10.

    Can this be done?

    As always, Thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    37,691

    Re: Name Range + Drop down list to crossmatch.

    Here's just one of many online tutorials telling you how to achieve dependent drop-down lists: https://excelsemipro.com/2011/05/a-d...list-in-excel/

    Have a go! By all means come back and ask if you run into difficulties.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    08-07-2019
    Location
    Sydney
    MS-Off Ver
    365 pro plus
    Posts
    17

    Re: Name Range + Drop down list to crossmatch.

    Thanks for the link AliGW,

    Can this be done without converting all my data into a table? one of the goals is each year i add 1500-2k rows of player data.

    I currently have done a 'name range' for Player_Grade =OFFSET('1st Grade'!$BK$1,MATCH('1st Grade'!$B10,'1st Grade'!$AS$2:$AS$19998,0),0,COUNTIF('1st Grade'!$AS$2:$AS$19998,'1st Grade'!$B10))
    and Player_Season =OFFSET('1st Grade'!$BL$1,MATCH('1st Grade'!$B10,'1st Grade'!$AS$2:$AS$19998,0),0,COUNTIF('1st Grade'!$AS$2:$AS$19998,'1st Grade'!$B10))


    A thought i had today was to simply add another MATCH formula into Player_Grade where cell 'O10' is read to see the year and then P10 would drop down the only possible options for that particular year.

    Does anyone have a thought or a solution?

    Thanks in advance

    Lumpy

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,586

    Re: Name Range + Drop down list to crossmatch.

    My thought would be go with the easier solution.
    I haven't looked at the link that Ali provided, however from the second sentence in post #3, I understand that it recommends converting the data to a table. Given the file already has approximately 4700 rows of data, and you plan to add up to another 2000 rows per year, it will still be 521 years before the table reaches the bottom of the sheet. 500 years from now Excel may have put more rows in a sheet so no worries.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    08-07-2019
    Location
    Sydney
    MS-Off Ver
    365 pro plus
    Posts
    17

    Re: Name Range + Drop down list to crossmatch.

    Thanks JeteMC

    Whatever you can recommend would be great. It was your assistance that helped me greatly last year with the name range for Player_Grade.

    I plan of trying AliGW table link today without the name range in place on another copy of my file.

    Lumpy

  6. #6
    Registered User
    Join Date
    08-07-2019
    Location
    Sydney
    MS-Off Ver
    365 pro plus
    Posts
    17

    Re: Name Range + Drop down list to crossmatch.

    Arvo all,

    I couldn't make the table work the way i was hoping. While the top row does have headings, they play no part in what i want. The most important information is running in column AY 2-20000. I am more in the belief that the OFFSET formula in name range is the way to go, as it drops downs a list for the players ID# against the many rows of data. I just need it to 'not include' the years not selected, if that makes sense. I'll try and propose the question another way.

    Lumpy

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    37,691

    Re: Name Range + Drop down list to crossmatch.

    If you do, do it here. Why not attach a workbook showing the problems you are having?

    Do not open a new thread on this issue, please - keep the conversation here.

  8. #8
    Registered User
    Join Date
    08-07-2019
    Location
    Sydney
    MS-Off Ver
    365 pro plus
    Posts
    17

    Re: Name Range + Drop down list to crossmatch.

    I did attach a workbook in my opening thread.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    37,691

    Re: Name Range + Drop down list to crossmatch.

    No, I mean an updated workbook showing the progress made so that we can troubleshoot it for you. You need to meet us halfway.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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