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

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.

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

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 DataValidation
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.

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.

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

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

"T. Valko" wrote:

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 DataValidation
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.

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.


.

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

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.


"Joe" wrote:

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

"T. Valko" wrote:

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 DataValidation
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.

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.


.

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

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.

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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 DataValidation
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.

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.


.

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

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:

InsertNameDefine
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)

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.


"Joe" wrote:

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

"T. Valko" wrote:

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 DataValidation
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.

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.


.

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

On Fri, 8 Jan 2010 18:53:18 -0500, "T. Valko"
wrote:

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:

InsertNameDefine
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/te...CT101172771033

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

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

your description here makes it understandable. Good job.

Thanks!

--
Biff
Microsoft Excel MVP


"WallyWallWhackr" <wallywallwhackr@thematrixattheendofthemushroomste m.org
wrote in message ...
On Fri, 8 Jan 2010 18:53:18 -0500, "T. Valko"
wrote:

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:

InsertNameDefine
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/te...CT101172771033

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