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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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
50,552
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
 

Forum statistics

Threads
1,141,144
Messages
5,704,544
Members
421,354
Latest member
Canary99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top