Change excel data validation 'list' properties based on a value selected. - Software Applications - MS Excel - TIK Share

Change excel data validation 'list' properties based on a value selected.


Babulal Shiradana

2013-12-20

Hi,

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!!

CA Pulkit Sharma

2013-12-20

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.

 Excel-List-validation.xlsx 

Reply

chand khan

2013-12-20

use this formula in custom list in data validation

=IF(B4="Others",OR(ISNUMBER(C5),ISTEXT(C5)))

 

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. 

Reply

CA Pulkit Sharma

2013-12-20

@chand

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.

Reply

chand khan

2013-12-20

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:

step1-Press Alt+D,L

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.

 

Reply

Babulal Shiradana

2013-12-21

Hi Pulkit and Chand,

Thanks you guys for the help.

@ Pulkit - the first step, to uncheck the error alert worked.

Reply