Data Validation - Alternative Lists

[Graeme]

I am designing a spreadsheet to send to non-excel users
and want to make it as easy as possible.

In one column I have a list box with three options
In the next column I want to be able to able to select an
item from another list. However, the options appearing in
the list are dependent upon what is chosen in the first
column. This means that I need three different lists.

How can I do this? Is it formula driven or do I use Data
Validation?

Thanks
G

 

[Frank Kabel]

Hi
have a look at
http://www.contextures.com/xlDataVal02.html
for dependent listboxes using data validation

HTH
Frank

 

[Arvi Laanemets]

Hi

Here is a copy-paste from an earlier posting from me, maybe you get some
ideas from this:

***
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
***