Selective Data Validation

  • Thread starter Thread starter monagan
  • Start date Start date
M

monagan

I have a drop down list from data validation.

I need it to be specific numbers based on another cell.
Like if a1=12
then the list is 1,2,3

if a1=15
then the list is 2,5,9,7

and so on..

I have realized that it won't accept an if statement in the source, so
how should I go about this?

any advice is appreciated.
 
Use the indirect function:

In Column B have

B1 : 1
B2 : 2
B3 : 3

In Column C have

B1 : 2
B2 : 5
B3 : 9
B4 : 7

D1 = IF(A1=12,"B1:B3",(IF(A1 = 15,"C1:C3")))

Your Validation formula will be =indirect(d1
 
If there is a limited number of lists, you could use an IF statement.
For example, enter the first list in cells F1:F3, and enter the second
list in cells G1:G4. In cell H1, type: N/A

Select cell B1, and choose Data>Validation
Choose to Allow: List
In the Source box, enter:
=IF(A1=12,$F$1:$F$3,IF(A1=15,$G$1:$G$4,$H$1))

If there are many lists, you could store them on a separate sheet, and
name the ranges. For example, Sheet2!A1:A3 could be named List12
Name the range that contains the N/A as "List"

Then, use the INDIRECT formula to refer to the range. In the Source box,
type: =INDIRECT("List"&A1)

There are instructions here:

http://www.contextures.com/xlDataVal02.html
 
Back
Top