Create a list with data validation for partial text

[NickNunes]

Howdy y'all

I currently have a table in which I have to choose a certain value from a list using the data validation method. But what I want to do is be able to choose something and type extra information to complement it. See attached file with example

Example screenshot.PNG

[AliGW]

Here's one well-trodden way: https://www.contextures.com/xlDataVal02.html

[NickNunes]

That wasn't really what I was looking for, let me try to explain differently.

In case subcontractor is the chosen option, I must be allowed to type something in the same cell and not get an error of "Invalid data". Basically what I need is that the cell validates any Subcontractor*.

[Glenn Kennedy]

"BUT, if I choose subcontractor I need to be able to type something to complement it."

Is too vague. Please give some concrete examples of what you want.

[NickNunes]

It's like in the picture I've posted. I chose "subcontractor" in the data validation cell, and what I need is the possibility to have an end result like the cell right next to it "subcontractor blah"

EDIT: I've done an example on my original table, I essentially need to be able to do what I have done on the very last cell without getting that error.

Example 2 screenshot.PNG

[Glenn Kennedy]

Try using this as the data validation formula in E2


=COUNTIF(E2,"*"&C2&"*")=1

[NickNunes]

Not quite what I need yet, but getting close. In the first photo I need C2 to be like E2. The second photo explains better what I'm looking for

[kvsrinivasamurthy]

Try this. Worksheet events is used.

Worksheet_SelectionChange event validates cell C2 with list A1:A3

When Subcontractor is selected worksheet_Change event deletes validation and changes or addition of text can be done.

Codes:

Please Login or Register  to view this content.

How to paste the code?

Right click on the worksheet tab.
View code
VB window opens
Paste the code in the window.
Close the window.

[NickNunes]

When I pasted in my spreadsheet it had a few issues, but I fixed them easily and other than that, this code works like a charm. I'll post my changes as a reference in case anyone else needs something similar:

Please Login or Register  to view this content.