Excel - usage of drop-down list

[Nico_Kum]

Hello,

I need some help regarding usage of drop-down list.

I have several excel sheets, each sheet put into specific folders on a server.

The first one is the main excel sheet where resides the full drop-down list, separated by groups into « Define Name «  like this example :

Each excel sheet (users sheet) has got another « Define Name » linked the the main file, like this :

and the drop-down list is set :

Sheet User2:

drop-down user2:

and so on, having more than 20 users linked like that.

My question is :

Each input into the drop-down list starts with a number ‘01’, ’02, etc.. these inputs may grow in the future, starting from 2-3 inputs now, being 30 or more choices into each list.

Saying now that my users are « lazy » and want to put « 01 » into a cell which will display the complete input « 01_###_###_AAA » when pressing Enter, instead of scrolling the complete list.

At this time, when trying to put '01' into a cell gives me this error :

Any idea how I can troubleshoot this ?

Many thanks

Nicolas

[#2]

If you expect your AAA, BBB and CCC lists to grow, make sure your ORIGINAL lists are formatted as table (three separate tables!). That way, the range names AAA, BBB and CCC will automatically extend when you add data to each table.
In my Excel if I enter 01, it autocompletes to the full text 01_###_###_ABC. All I then have to do is press Tab or Enter to enter it into the cell.

[#3]

@Jan Karel Pieterse 

Hello Jan Karel,
Thank you for your prompt reply and yes, I forgot the tables.. many thanks.
But I tested again inserting the '01' instead of using the button list and I still receive the same error.
I checked my Excel version, I'm on a Win10 Enterprise with Microsoft 365 MSO ver. 2308.
I will do some more tests but if you have some good advice, I'm always interested.
Many thanks,
Have a nice day
Nicolas

[#4 · Accepted solution]

@Nico_Kum It is likely your channel hasn't received that Validation update yet. It has been released to Monthly channel, but chances are you are on (semi) annual and have to wait some more months before it arrives.

[#5]

@Jan Karel Pieterse 

Thank you for the update, yes, all updates into my organisation is managed by the IT Team, I do not have any rights or access to.
I will keep an eye on it.

Thank you again, 

Have a nice day

Nicolas