How to return a range of values in a drop-down.

[Joe]

Is there a way to have Excel return a range of values in a drop down in one
cell based on input from another cell.
As an example:

Cell A1 has the text "PVC Pipe"

I want cell A2 to get input from A1, read through a table, and return the
corresponding values (sizes) of PVC pipe. The results in A2 would be the
following in a drop down:

4"
6"
8"

If cell A1 contained the text "Metal Pipe" it would return values (sizes)
of metal pipe from a table and not display the sizes of PVC pipe.

10"
12"
15"

The bottom line and tricky part for me is how to have Excel return a range
of values back to me in a drop down. I've recently learned how to use
Vlookup, Index & Match. I want to do something along these lines, however
the results displayed will be more than one value.

 

[T. Valko]

If you only have those 2 categories...

List the selections for each category in a range of cells...

F1:F3 = values that correspond to PVC pipe
G1:G3 = values that correspond to Metal pipe

Setup the drop down list...

Select cell A1
Goto the menu Data>Validation
Allow: List
Source:

=IF(A1="PVC Pipe",F1:F3,IF(A1="Metal Pipe",G1:G3,NA()))

OK out

If you get a message saying the "The source currently evaluates to an
error....", just answer Yes.

 

[Joe]

That did the trick. Thanks for your help. By the way, what does the "NA()"
in the formula do?

 

[Joe]

One last thing, in my example, I used two inputs. What if I have a larger
number of inputs, say 10? The data validation limits what I can type into it.

 

[T. Valko]

By the way, what does the "NA()" in the formula do?

If the input cell doesn't contain one of the two entries, either PVC or
Metal, then the NA() will cause the formula to return an error and the drop
down won't work.

 

[T. Valko]

One way...

Create a table that lists all the input categories and their corresponding
values. Like this:

.......C.......D........E
1...Cat1...Cat2...Cat3
2...v1.......v1.......v1
3...v2.......v2.......v2
4...v3.......v3.......v3

Then, create a series of defined names like this:

Insert>Name>Define
Name: Cat1
Refers to: =$C$2:$C$4

Name: Cat2
Refers to: = $D$2:$D$4

Name: Cat3
Refers to: =$E$2:$E$4

OK out

Then, with cell A1 as the input cell:

A1 will contain either Cat1, Cat2 or Cat3

As the source for your drop down use:

=INDIRECT(A1)

 

[WallyWallWhackr]

One way...

Create a table that lists all the input categories and their corresponding
values. Like this:

......C.......D........E
1...Cat1...Cat2...Cat3
2...v1.......v1.......v1
3...v2.......v2.......v2
4...v3.......v3.......v3

Then, create a series of defined names like this:

Insert>Name>Define
Name: Cat1
Refers to: =$C$2:$C$4

Name: Cat2
Refers to: = $D$2:$D$4

Name: Cat3
Refers to: =$E$2:$E$4

OK out

Then, with cell A1 as the input cell:

A1 will contain either Cat1, Cat2 or Cat3

As the source for your drop down use:

=INDIRECT(A1)

Yes, I use named ranges as a drop down list definition as well, but
your description here makes it understandable. Good job.

I do a similar thing with a time sheet I made that allows the user to
select from various time increments, and the drop down list varies
accordingly. I think I went about it slightly differently though.

It is on the MicroSoft Office Template site at:

http://office.microsoft.com/en-us/templates/TC300083091033.aspx?pid=CT101172771033

There are several lists (some long) from which the increment is
derived.

 

[T. Valko]

your description here makes it understandable. Good job.

Thanks!