Combo box list depends on value
 

Hi there

We have a sheet that allows people to select items from a list. Each item
they pick has a value associated. There are 6 rows of this, then a total
appears.

The next combo box list text depends on the total from the previous choices.
If they score from 0-6 the first list should appear, from 7-12 a different
list appears etc

How can we do this? At the moment we are using Excel 2003 and Data
Validation Lists, but not sure if this will do what we want.

Regards, Neal.

Hi

Define those various lists a named ranges, p.e. List1, List2, ..., List6
Let you have your total be in cell A7

For next data validation list, set source to:
=CHOOSE(INT(MAX(1;(C1+5)/6)),List1,List2,List3,List4,List5,List6)
(You can have up to 24 various lists to select from in formula)


Arvi Laanemets


"Neal" <neal_@_ap0yar_dot_co_dot_nz wrote in message
...
Hi there

We have a sheet that allows people to select items from a list. Each item
they pick has a value associated. There are 6 rows of this, then a total
appears.

The next combo box list text depends on the total from the previous
choices.
If they score from 0-6 the first list should appear, from 7-12 a different
list appears etc

How can we do this? At the moment we are using Excel 2003 and Data
Validation Lists, but not sure if this will do what we want.

Regards, Neal.

Thanks for your reply.

What is the C1+5/6 for? What do you have in col C?

Thanks, Neal.


"Arvi Laanemets" wrote in message
...
Hi

Define those various lists a named ranges, p.e. List1, List2, ..., List6
Let you have your total be in cell A7

For next data validation list, set source to:
=CHOOSE(INT(MAX(1;(C1+5)/6)),List1,List2,List3,List4,List5,List6)
(You can have up to 24 various lists to select from in formula)


Arvi Laanemets


"Neal" <neal_@_ap0yar_dot_co_dot_nz wrote in message
...
Hi there

We have a sheet that allows people to select items from a list. Each
item
they pick has a value associated. There are 6 rows of this, then a total
appears.

The next combo box list text depends on the total from the previous
choices.
If they score from 0-6 the first list should appear, from 7-12 a
different
list appears etc

How can we do this? At the moment we are using Excel 2003 and Data
Validation Lists, but not sure if this will do what we want.

Regards, Neal.

Hi

It look like it's time to bed for me :-))

Instead C1 use A1 there, i.e. your sum. And look for semicolons in formula
too - replace them with commas.
INT((A7+5)/6) returns 1 for A7 values 1-6, 2 for A7 values 7-12, etc. The
MAX(1,...) is for case A7=0


Arvi Laanemets


"Neal" <neal_@_ap0yar_dot_co_dot_nz wrote in message
...
Thanks for your reply.

What is the C1+5/6 for? What do you have in col C?

Thanks, Neal.


"Arvi Laanemets" wrote in message
...
Hi

Define those various lists a named ranges, p.e. List1, List2, ..., List6
Let you have your total be in cell A7

For next data validation list, set source to:
=CHOOSE(INT(MAX(1;(C1+5)/6)),List1,List2,List3,List4,List5,List6)
(You can have up to 24 various lists to select from in formula)


Arvi Laanemets


"Neal" <neal_@_ap0yar_dot_co_dot_nz wrote in message
...
Hi there

We have a sheet that allows people to select items from a list. Each
item
they pick has a value associated. There are 6 rows of this, then a
total
appears.

The next combo box list text depends on the total from the previous
choices.
If they score from 0-6 the first list should appear, from 7-12 a
different
list appears etc

How can we do this? At the moment we are using Excel 2003 and Data
Validation Lists, but not sure if this will do what we want.

Regards, Neal.