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

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

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

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

I did attach a workbook in my opening thread.

JeteMc,

Some slight modify but it has worked a treat, Many thanks Sir.

AilGW, thank you for your suggestion and time, While it didn't work out the way i wanted, I still learnt something new which is always a big plus in my books.

I'm always trying to understand new formulas through this forum or even YouTube, I'm primarily self taught

Lumpy