Make a choice in an "extensible" list

Hello,

I have to introduce often the same entry (i.e. a firm or commerce name).
To avoid having to make all te time the same entries and to avoid entring the same firm with different spellings I imagine making a choice from a list, list that would be able to "learn" new entries.

Example: When I type the "D" of "Dupont de Nemours", Excel would propose all entries beginning with a "D", and if I introduce a name that doesn't still exist in the list, I should be made attentive to that fact and proposed to add that name to the list.

Is that possible? How?

Thanks for all hints.

Regards

Aloha

Hello Carim

thank you very much for your quick response.

Best regards

Aloha

Hello Carim, once again,

A restriction in the use of validation makes a problem to me:the fact that the list has to be in the same sheet!

My situation: I have a WB with a dozen of sheets with the same structure where a dozen persons make entries of the same kind (bookkeeping).

If on can not bypass this restriction, that means that I have to have a list per person, instead of one list for all.

What do you think about that?

Regards

Aloha

Hello Carim,

isn't it a problem that the introduction of the data is made in a dozen sheets?

Aloha

Hi Aloha (or, should I say Aloha, ALOHA?),

Sorry to enter this conversation in mid-stream. But, maybe I can help before Carim is back.

isn't it a problem that the introduction of the data is made in a dozen sheets?

Yes and No.
No, because all of the validation cells and combo boxes will refer to the same list.

Yes, because the Event code (i.e., Worksheet_BeforeDoubleClick and Worksheet_SelectionChange) would need to be on each sheet. UNLESS, instead of using these Events (which are related to individual worksheets), you used the equivalent Events, but at a workbook level:
Workbook_SheetBeforeDoubleClick
Workbook_SheetSelectionChange

How far along are you in getting this implemented?
Can you post a skeleton copy of your workbook to this forum, with some dummy data in it? (To post it here, you would need to first save it as a Zip file and upload the Zip file.)

Hi,

Thank you for your offer.
I post en example file. The text is in french, since I'm here in Luxemburg.

The sheet "Fournisseurs" has to contain the auto-extensible list.
In the sheets "Agent1" to "Agent3" the data is introduced, and for this purpose it's the column "Fournisseur" that is interesting.

If you have any question about it, I'm at your disposition.

Regards

Aloha

Got it. Back in a few.

Hi,

Thanks for answering so quickly after getting my file.

But I have to go to bed now. Until tomorrow.

Good night (but I don't know the time it is where you live. Where is that? In the States?)

Bye bye

Aloha

Looks like you might have two different versions of this when you wake up.

Made a few assumptions that might not be correct ...
Assume that you want the list to come from cells A2:A7, and that this list will grow.

Created a name “Fournisseur”. The RefersTo for this Name is the formula:
=OFFSET(Fournisseurs!$A$2,0,0,COUNTA(Fournisseurs!$A:$A),1)

Added Data Validation to range D4:D52 on each of the three Agent sheets

Took the code that Carim suggested and changed it to be workbook-level, as noted earlier. So, all of that code is in the ThisWorkbook object’s code window.

Seems it works. The combo box display upon double-click of a cell with data validation. But, only if the CODENAME of the sheet is like “Feuil*”

The combobox will auto-complete and will put the result back in the cell.

To get the combo box to disappear, you have to either click in a cell or switch to another sheet.

Also, added code to ensure that all agent worksheets have a combo box. Right now, I am doing that at workbook_open, and it is based on the sheet having CODENAME of “Feuil*”.

If you have no idea what I mean by CODENAME of “Feuil*”, then I’ll change that for you. I just noticed this feature and assumed it was of your design.

Hello MSP...,

Thank you very much for your precious help.

Regards

Aloha

Hello,

What do I have to do to convince Excel to propose systematically an entry, when I begin typing the first letters of a name already existing in the list?

Regards

Aloha

What do I have to do to convince Excel to propose systematically an entry, when I begin typing the first letters of a name already existing in the list?

When you DOUBLE-CLICK in one of the cells with Validation drop-down list, a ComboBox should appear, and that ComboBox is set up to include auto-completion.

to help further, I need to know which step is not working for you ...

Are you using the workbook I uploaded yesterday?
Are you double-clicking?
Is the ComboBox appearing?
Does it autocomplete?

Hello MSP*,

It isn't possible to enter an other name than those already present in the list, while the purpose of the solution I'm looking for is to be able to select a name in the list, to enter a new name in the cell if it isn't already in the list and to get Excel adding it to the list in order to be selectable the next time I have to enter a name.

The cells included in the validation include only the existing names.

Regards

Aloha

P.S.: I'm so late to answer because I'm doing almost a dozen things at the same time.

Hello Carim

Thank you for the nearly instant response.
Yes, of course I looked at it, but I didn't manage to adapt it to my concrete situation.

Regards

Aloha