Drop down list with selection containing Alpha and Numeric characters not working

Macca1962

New Member
Joined
Dec 3, 2024
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
Hi, Is it possible to select data from a drop down list containing both alpha and numeric characters if they are all not in the exact same format

I am trying to pull some statistics from a football competition that had two seasons in one year (2022) and tried to differentiate them by using 2022 for the first season in 2022 and 2022B for the second season.

My list has

Option 1
2017 - corresponds to Season 1 or 2017A below
2018 (corresponds to Season 2 or 2018A below)
2019 (corresponds to Season 3 or 2019A below)
2020 (corresponds to Season 4 or 2020A below)
2021 (corresponds to Season 5 or 2021A below)
2022 (corresponds to Season 6 or 2022A below)
2022B (corresponds to Season 7 or 2022B below)
2023 (corresponds to Season 8 or 2023A below)

If i use the drop down list above it will only extract data for 2022B from the drop down list. All other values do not show any data but do NOT display any error messages either.

However if I change the data to the following I can extract the data for all Seasons 1 through 8

Option 2
Season 1
Season 2
Season 3
Season 4
Season 5
Season 6
Season 7
Season 8

and if I change the data to the following I can extract the data for all Seasons 2017A through 2023A

Option 3
2017A
2018A
2019A
2020A
2021A
2022A
2022B
2023A

Screenshots 1 (source data), 2 and 3 are for Option 1. Screenshot 2 is what occurs for 2017, 2018, 2019, 2020, 2021, 2022 and 2023. Screenshot 3 is what occurs for 2022B (this is what it should display).

Screenshots 4 (source data) and 5 are for Option 2. Screenshot 5 is what occurs for Season 1 through Season 8 (this is what it should display)

Screenshots 6 (source data) and 7 are for Option 3. Screenshot 7 is what occurs for 2017A, 2018A, 2019A, 2020A, 2021A, 2022A, 2022B and 2023A (this is what it should display)

Note - I only have formulas for Allan, Sarah in Screenshots 2, 3, 5 and 7 (I wanted to see if this will work with Option 1 before copying the formulas to other lines)

I just need some way to differentiate between Seasons 2022 and 2022B.

I am happy enough to use the Option 3 if this is a shortcoming of Excel (although this is going to cause a lot of pain adjusting the data in numerous spreadsheets)

I am sure it is not my formula as it is extracting all the data I need for Option 2 and Option 3 (all items in the drop down list) and only for 2022B in Option 1 (this proves the formula works so I think it might be because the data in the drop down list is not the exact same format for every line).

Hoping someone can confirm if Option 1 is possible and if so how I can get it to work.

Regards, Andrew
 

Attachments

  • Screenshot 1.png
    Screenshot 1.png
    66.7 KB · Views: 6
  • Screenshot 2.png
    Screenshot 2.png
    22.5 KB · Views: 6
  • Screenshot 3.png
    Screenshot 3.png
    26.2 KB · Views: 5
  • Screenshot 4.png
    Screenshot 4.png
    78.1 KB · Views: 5
  • Screenshot 5.png
    Screenshot 5.png
    22.9 KB · Views: 5
  • Screenshot 6.png
    Screenshot 6.png
    69.8 KB · Views: 5
  • Screenshot 7.png
    Screenshot 7.png
    25.1 KB · Views: 5

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
I am sure it is not my formula
Nevertheless, it would be helpful to see your formula.

It's possible to do.

Book1
ABCDEFGH
1DataResultDropdownResult
22017120182
3201822022B7
420193
520204
620215
720226
82022B7
920238
10
Sheet1
Cell Formulas
RangeFormula
H2:H3H2=XLOOKUP(G2,$A$2:$A$9,$B$2:$B$9)
Cells with Data Validation
CellAllowCriteria
G2:G3List=$A$2:$A$9
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,505
Messages
6,179,153
Members
452,891
Latest member
JUSTOUTOFMYREACH

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
Back
Top