conditionally restricting values in excel

  • Thread starter Thread starter __jakal__
  • Start date Start date
J

__jakal__

Hello,
I want to restrict values in a particular column based on the value in other
column. For eg.

if column A has value int then column B should be restricted to values
1,2,3,4,5
if column A has value bool then column B should be restricted to values true
and false.
if column A has value string I dont want any validation to
be done and the list box should not appear.
Is this possible.....

How do I achieve this ? Can I use macros to do this? How?

Thanks
 
In a column in the workbook, enter the values 1-5
Select the cells, and name the range, e.g. IntegerList
There are instructions for naming ranges on my web site:
http://www.contextures.com/xlNames01.html

In another column, enter TRUE and FALSE
Select the cells, and name the range, e.g. BooleanList

Select a blank cell, and name it NoList

Select the cells to receive data validation (e.g. B2:B100)
Choose Data>Validation
Choose to Allow: List
In the formula bar, type:


=IF(AND(ISNUMBER(A2),MOD(A2,INT(A2))=0),IntegerList,IF(ISLOGICAL(A2),BooleanList,NoList))

Click OK

If an integer is in column A, values 1-5 will be in the list
If a boolean is in column A, TRUE and FALSE will be in the list
If anything else is in column A, the dropdown arrow will appear, but no
list. Users can type any value in the cell.
 
Back
Top