Hi, all, I have a XL worksheet for user to enter data. In cell C11, I have a
validation rule to allow user to pick value from a list. I want to set up a
validation rule in cell D11 (wherever) and the available list of value to
choose depends on the value the user chose in cell C11. That means if user
choose 1 in cell C11, the validation rule in cell D11 will become a list of
"AA,BB,CC". And if user choose 2 in cell C11, the available list of value
will become "DD,EE,FF". Can anybody tell me how to do it? Do I need to write
a VBA script and how to?

Thanks in advance.

Huyeote

Take a look at Debra Dalgleish's site:

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

Huyeote wrote:

Hi, all, I have a XL worksheet for user to enter data. In cell C11, I have a
validation rule to allow user to pick value from a list. I want to set up a
validation rule in cell D11 (wherever) and the available list of value to
choose depends on the value the user chose in cell C11. That means if user
choose 1 in cell C11, the validation rule in cell D11 will become a list of
"AA,BB,CC". And if user choose 2 in cell C11, the available list of value
will become "DD,EE,FF". Can anybody tell me how to do it? Do I need to write
a VBA script and how to?

Thanks in advance.

Huyeote

--

Dave Peterson

Hi,

no need for VBA, you can do it with worksheet formulas...

make 2 named ranges.

a named range e.g.
valTable , refto =sheet2!a1:e30
valList, refto =offset(valTable;0;sheet1!a1;;1)


Then point the source of your validation to the named range valList
(pressing f3 in the source box brings up the named ranges in your book.


now if sheet1!a1=0 it will take the first column in valTable.
make it 2 and you'll have the 3rd column in the table.

check out the Offset function in excel help. It's VERY usefull.


let me know it you get it to work.. :)


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Huyeote" wrote:

Hi, all, I have a XL worksheet for user to enter data. In cell C11, I
have a validation rule to allow user to pick value from a list. I want
to set up a validation rule in cell D11 (wherever) and the available
list of value to choose depends on the value the user chose in cell
C11. That means if user choose 1 in cell C11, the validation rule in
cell D11 will become a list of "AA,BB,CC". And if user choose 2 in
cell C11, the available list of value will become "DD,EE,FF". Can
anybody tell me how to do it? Do I need to write a VBA script and how
to?

Thanks in advance.

Huyeote