Change excel data validation 'list' properties based on a value selected.
my Requirement is i have a set of 6 values that i need to give as dropdown values in excel, user should be able to choose anyone of the 5, the 6th one is 'others', so when user selects 'others', the user should be allowed to enter a text.
can this be achieved in excel ? if yes can you give me clear steps ?
Thanks in advance!!
Hi, here is the solution:
Once you have done the validation. Select the validated cell.
Now click on DATA>DATA Validation
Select Error Alert Tab
And uncheck Show error alert after invalid data is entered.
Your problem is solved hopefully.
Attaching file for your help.
use this formula in custom list in data validation
B4 is your drop downlist and c5 is the cell inwhich you want to put values
but it will only allow user to either put numeric values or text values.
He wants value in the same drop down cell.
The formulla you gave, helps only to change the format of cell c5.
I believe what he wants is suppose in B5 dropdown list, if user instead of selecting 1,2,3,4,5, he selects others than he should be able to enter anyvalue.
@ Chand, Is there any function which can enable him to input anytext if users select other from drop down list? Even i want to know this. Intresting.
i think he have a list of values including "others", and if anyone select others in list then user should be allowed to enter any value in cell otherwise not.
for this he can use below steps:
step2-Select Cutom from a list
step3- use this formula =IF(B4="Others",LEN(C5)<>0,"") change B4 to his drop down list and c5 to the cell in which he wants to allow user to put text
then click ok.
Hi Pulkit and Chand,
Thanks you guys for the help.
@ Pulkit - the first step, to uncheck the error alert worked.