Select from List (or Validation) but can overtype.

[wgb_one]

I have got a cell with a data validation in.

The user selects an option from the the dropdown list and dependent on what they select I have vlookup in an adjacent cell that categoises their selection.

But I want the option that they can overtype the validation and the category selected = "Other"

The dropdown box is in cell A4

The List for the validations is j2:j5 with the corresponding vlookup in the adjecent cells j2:k5

Option - Category
A = x
B = y
C = z
"type" = other

Any ideas how I can do this?

Cheers

 

[NBVC]

update your formula to include a Match search...


e.g. =IF(ISNA(MATCH(A4,J2:J4,0)),"other",your formula here for match found)

Note: you will have to uncheck the error alert message in the Error Alert tab of the Data Validation dialogue.

 

[Peter_SSs]

But I want the option that they can overtype the validation and the category selected = "Other"

Not quite sure what you mean - Can you explain this is more detail?

 

[wgb_one]

Clarification

The spreadsheet is part of an employee appraisal.

The employee selects a response to a question - if the employee selects on of the standard responses then they are selected from the drop down box.

If they select one of the responses (from the dropdown hence the validation) - they are scored (hence the vlookup - linked to the response from the dropdown)

However I do not want to restrict the emploee fully and they can overtype the the option and the score = "OTHER"

Option(dropdown) = Score (vlookup)
Once = S
Twice = P
"freetype = O


Hope this makes sense.

 

[wgb_one]

Clarification

The spreadsheet is part of an employee appraisal.

The employee selects a response to a question - if the employee selects on of the standard responses then they are selected from the drop down box.

If they select one of the responses (from the dropdown hence the validation) - they are scored (hence the vlookup - linked to the response from the dropdown)

However I do not want to restrict the emploee fully and they can overtype the the option and the score = "OTHER"

Option(dropdown) = Score (vlookup)
Once = S
Twice = P
"freetype = O


Hope this makes sense.

 

[NBVC]

Re: Clarification

The spreadsheet is part of an employee appraisal.

The employee selects a response to a question - if the employee selects on of the standard responses then they are selected from the drop down box.

If they select one of the responses (from the dropdown hence the validation) - they are scored (hence the vlookup - linked to the response from the dropdown)

However I do not want to restrict the emploee fully and they can overtype the the option and the score = "OTHER"

Option(dropdown) = Score (vlookup)
Once = S
Twice = P
"freetype = O


Hope this makes sense.

Did you review my suggestion above...or is it not what you are after?

e.g.
=IF(ISNA(MATCH(A4,J2:J4,0)),"O",Vlookup(A4,J2:K4,2,0))

...you may have to adjust the Vlookup() formula to suit your table.

 

[wgb_one]

Thanks NBVC - I had to change the fornula slightly but it works a treat.

Cheers