Select from List (or Validation) but can overtype.

wgb_one

New Member
Joined
Dec 18, 2002
Messages
11
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,249
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 18, 2002
Messages
11
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

New Member
Joined
Dec 18, 2002
Messages
11

ADVERTISEMENT

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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

New Member
Joined
Dec 18, 2002
Messages
11
Thanks NBVC - I had to change the fornula slightly but it works a treat.

Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,109,043
Messages
5,526,423
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top