hlookup to propigate validation table

For creating dependent lists with Data Validation see Debra Dalgleish's site.

http://www.contextures.on.ca/xlDataVal02.html

Gord Dibben XL2002

On Mon, 10 Nov 2003 02:25:09 -0500, timpadden
wrote:


I am looking to use hlookup to to propigate what goes into my validation
data list. I have tried to enter the hlookup equation into the
validation shource wiht no luck. I want the choice made in cell A:1 (
that has a validation list) to change the data available in the B:1
pull down validation list using hlookup that refers to a table . this
means if you pull down value christmas candle in list A:1 , validation
list B:1 will give you the values (color options) of red and green to
choose from. If you choose Halloween from the pull down in A:1 , B:1
will give you the colore options of orange and black. I have managed to
do this in a very round about way but it is not feasable with the
amount of data I am working with.
if you want to take a look http://padden.home.texas.net/Lorena/
its on the poruction de velas page . I have all the possible colors
listed but I want to narrow down the choices in column E from the
choice in D


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Hi

NB! The following is working only for one pair of validation lists!

On Sheet1
Column A
A1=header (List1)
From A2 down enter list values
Create a named range
List1=OFFSET(Sheet1!$A$2,,,COUNTIF(Sheet1!$A:$A," ")-1,1)

On Sheet2, format A1 using Data.Validation.List with Source=List1

On Sheet1
Starting from column C, for every entry in List1, enter available values for
List2 into column, with value from List1 as header. I.e. when in A2 is the
entry 'Christmas candle', then into cell C1 enter also 'Christmas candle',
and texts 'red' and 'green' in C2:C3. And with 'Halloween' in A3, you have
'Halloween', 'orange' and 'black' in D13, etc. Btw., you don't be limited
to even number of list members in List2.
Create a named range
List2=OFFSET(Sheet1!$B$2,,MATCH(Sheet2!$A$1,List1) ,COUNTIF(OFFSET(Sheet1!$B$
2,,MATCH(Sheet2!$A$1,List1),1000,1),""),1)
the number 1000 in offset's you can change with some other reasonable one,
but it must be big enough to include the longest possible list.

On Sheet2, format B1 using Data.Validation.List with Source=List2
It's all


--
(When sending e-mail, use address )
Arvi Laanemets

"timpadden" wrote in message
...

I am looking to use hlookup to to propigate what goes into my validation
data list. I have tried to enter the hlookup equation into the
validation shource wiht no luck. I want the choice made in cell A:1 (
that has a validation list) to change the data available in the B:1
pull down validation list using hlookup that refers to a table . this
means if you pull down value christmas candle in list A:1 , validation
list B:1 will give you the values (color options) of red and green to
choose from. If you choose Halloween from the pull down in A:1 , B:1
will give you the colore options of orange and black. I have managed to
do this in a very round about way but it is not feasable with the
amount of data I am working with.
if you want to take a look http://padden.home.texas.net/Lorena/
its on the poruction de velas page . I have all the possible colors
listed but I want to narrow down the choices in column E from the
choice in D


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/