Combo box list depends on value

  • Thread starter Thread starter Neal
  • Start date Start date
N

Neal

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
 
Thanks for your reply.

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

Thanks, 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
 
Back
Top