MrExcel Publishing
Your One Stop for Excel Tips & Solutions

how to create optional list based on values in anothers cell reference


Posted by fajar on January 15, 2002 1:49 AM

i have i question, about how to create 'advanced' list (validation) where the list can be different on the cell based on other's value cell.. it's a way to do it ?


Posted by Aladin Akyurek on January 15, 2002 2:45 AM

Lets say that you want to feed data validation a 'source' which is specified as range or the name of a relevant range.

Let D1 specify a range, say, $G$1:$G$6 or Alist that refers to (is the name of) G1:G6.

Lets have a data validation list in A1.

Activate A1.
Activate Data|Validation.
Chose List.
Enter as Source:

=INDIRECT(D1)

Now, if you change what is in D1, that change will be immediately reflected in A1.

Aladin