Dependent Dropdown List - Large Scale

H

Henn9660

Good Morning Everyone!

I've read all of the 'Contextures Articles' on Dependent Dropdowns, but they
do not seem to address what I am specifically trying to accomplish.

I have a 4000-line spreadsheet used to track hardware and peripherals.
Column A (Category) invokes Data Validation to a list of 39 Hardware Types.

Column B2, for example, would contain the name of the Manufacturer for the
Hardware Type selected in A2 and so on through A4000.

The individual Manufacturer Names [for each of the 39 Hardware Types] in
Category are listed in Columns AA through BM. Three of the Categories I use
are Modem, Projector, and Hub.

What I'm looking for (hopefully) would be a way to use a "large" IF
statement that challenges the dropdown values chosen in A2-A4000 to the
appropriate Manufacturer Names to be dropdown-displayed in B2...B4000.

The 39 Hardware Types are contained [out of sight] in Columns AA-BM.

What I would like to do is something like:

IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers)
OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector Manufacturers)
OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers)
OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on until
OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is Switch)

Any thoughts would be most greatly appreciated!

Thanks,
Jeff in Mississippi
 
T

T. Valko

What you need to do is create named ranges for each of the hardware types.

For example:

Named range: Modem
Refers to: AE2:AE20

Then, as the source for the drop down in B2 use this formula:

=INDIRECT(A2)
 
H

Henn9660

Thanks for the info. I already entered the Name Ranges for each of the 39
types.

A couple of days ago, I used "Indirect" to test each of the 39 Hardware
Types and it worked fine.

However, I then went to line A40 and entered one of the previous values
again, only to find that the dropdown for B40 did not 'carry over' the
"Indirect" value from above for the Hardware Type listed at A40. Any
thoughts? Thx, Jeff

T. Valko said:
What you need to do is create named ranges for each of the hardware types.

For example:

Named range: Modem
Refers to: AE2:AE20

Then, as the source for the drop down in B2 use this formula:

=INDIRECT(A2)



--
Biff
Microsoft Excel MVP


Henn9660 said:
Good Morning Everyone!

I've read all of the 'Contextures Articles' on Dependent Dropdowns, but
they
do not seem to address what I am specifically trying to accomplish.

I have a 4000-line spreadsheet used to track hardware and peripherals.
Column A (Category) invokes Data Validation to a list of 39 Hardware
Types.

Column B2, for example, would contain the name of the Manufacturer for the
Hardware Type selected in A2 and so on through A4000.

The individual Manufacturer Names [for each of the 39 Hardware Types] in
Category are listed in Columns AA through BM. Three of the Categories I
use
are Modem, Projector, and Hub.

What I'm looking for (hopefully) would be a way to use a "large" IF
statement that challenges the dropdown values chosen in A2-A4000 to the
appropriate Manufacturer Names to be dropdown-displayed in B2...B4000.

The 39 Hardware Types are contained [out of sight] in Columns AA-BM.

What I would like to do is something like:

IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers)
OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector Manufacturers)
OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers)
OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on until
OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is
Switch)

Any thoughts would be most greatly appreciated!

Thanks,
Jeff in Mississippi
Click to expand...
Click to expand...
 
T

T. Valko

Not sure what you mean by "carry over" ?

Did you set B40 to use data validation?

--
Biff
Microsoft Excel MVP


Henn9660 said:
Thanks for the info. I already entered the Name Ranges for each of the 39
types.

A couple of days ago, I used "Indirect" to test each of the 39 Hardware
Types and it worked fine.

However, I then went to line A40 and entered one of the previous values
again, only to find that the dropdown for B40 did not 'carry over' the
"Indirect" value from above for the Hardware Type listed at A40. Any
thoughts? Thx, Jeff

T. Valko said:
What you need to do is create named ranges for each of the hardware
types.

For example:

Named range: Modem
Refers to: AE2:AE20

Then, as the source for the drop down in B2 use this formula:

=INDIRECT(A2)



--
Biff
Microsoft Excel MVP


Henn9660 said:
Good Morning Everyone!

I've read all of the 'Contextures Articles' on Dependent Dropdowns, but
they
do not seem to address what I am specifically trying to accomplish.

I have a 4000-line spreadsheet used to track hardware and peripherals.
Column A (Category) invokes Data Validation to a list of 39 Hardware
Types.

Column B2, for example, would contain the name of the Manufacturer for
the
Hardware Type selected in A2 and so on through A4000.

The individual Manufacturer Names [for each of the 39 Hardware Types]
in
Category are listed in Columns AA through BM. Three of the Categories
I
use
are Modem, Projector, and Hub.

What I'm looking for (hopefully) would be a way to use a "large" IF
statement that challenges the dropdown values chosen in A2-A4000 to the
appropriate Manufacturer Names to be dropdown-displayed in B2...B4000.

The 39 Hardware Types are contained [out of sight] in Columns AA-BM.

What I would like to do is something like:

IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers)
OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector
Manufacturers)
OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers)
OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on until
OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is
Switch)

Any thoughts would be most greatly appreciated!

Thanks,
Jeff in Mississippi
Click to expand...
Click to expand...
Click to expand...
 
H

Henn9660

I apologize if I am unclear..... (it must be frustration setting in (LOL)).

Let me restate the situation. Column A extends to 4000 lines, max. The
permissible values in Column A (dropdown is called CATEGORY) consist of 1-39
values in the CATEGORY dropdown list. Column B (dropdown is called MFR)
represents the Manufacturer of the Hardware listed in Column A. Each of the
39 CATEGORY entries has its own unique list of MANUFACTURERS.

(The Category lists contains entries such as VPN, Hub, Modem, PatchPanel,
UPS, Server, etc.)

For example if users selected the dropdown value of MODEM in A2, A44, A525,
A600, A1100, A1555, and A2399 the associated dropdown appearing in Column B
(at B2, B44, B525, B600, B1100, B1555, and B2399) would appear without having
to enter INDIRECT statements singly. With possibly 15 different people
entering data, these "automatic-directed-dropdown" would be invaluable.

LOL, I hope this is clearer!

Thx,
Jeff



T. Valko said:
Not sure what you mean by "carry over" ?

Did you set B40 to use data validation?

--
Biff
Microsoft Excel MVP


Henn9660 said:
Thanks for the info. I already entered the Name Ranges for each of the 39
types.

A couple of days ago, I used "Indirect" to test each of the 39 Hardware
Types and it worked fine.

However, I then went to line A40 and entered one of the previous values
again, only to find that the dropdown for B40 did not 'carry over' the
"Indirect" value from above for the Hardware Type listed at A40. Any
thoughts? Thx, Jeff

T. Valko said:
What you need to do is create named ranges for each of the hardware
types.

For example:

Named range: Modem
Refers to: AE2:AE20

Then, as the source for the drop down in B2 use this formula:

=INDIRECT(A2)



--
Biff
Microsoft Excel MVP


Good Morning Everyone!

I've read all of the 'Contextures Articles' on Dependent Dropdowns, but
they
do not seem to address what I am specifically trying to accomplish.

I have a 4000-line spreadsheet used to track hardware and peripherals.
Column A (Category) invokes Data Validation to a list of 39 Hardware
Types.

Column B2, for example, would contain the name of the Manufacturer for
the
Hardware Type selected in A2 and so on through A4000.

The individual Manufacturer Names [for each of the 39 Hardware Types]
in
Category are listed in Columns AA through BM. Three of the Categories
I
use
are Modem, Projector, and Hub.

What I'm looking for (hopefully) would be a way to use a "large" IF
statement that challenges the dropdown values chosen in A2-A4000 to the
appropriate Manufacturer Names to be dropdown-displayed in B2...B4000.

The 39 Hardware Types are contained [out of sight] in Columns AA-BM.

What I would like to do is something like:

IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers)
OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector
Manufacturers)
OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers)
OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on until
OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is
Switch)

Any thoughts would be most greatly appreciated!

Thanks,
Jeff in Mississippi
Click to expand...
Click to expand...
Click to expand...
 
T

T. Valko

Sorry, I'm not following you on this.

It sounds like you want the dependent drop down to appear in column B
without having to "define" a source. That's not possible.

--
Biff
Microsoft Excel MVP


Henn9660 said:
I apologize if I am unclear..... (it must be frustration setting in (LOL)).

Let me restate the situation. Column A extends to 4000 lines, max. The
permissible values in Column A (dropdown is called CATEGORY) consist of
1-39
values in the CATEGORY dropdown list. Column B (dropdown is called MFR)
represents the Manufacturer of the Hardware listed in Column A. Each of
the
39 CATEGORY entries has its own unique list of MANUFACTURERS.

(The Category lists contains entries such as VPN, Hub, Modem, PatchPanel,
UPS, Server, etc.)

For example if users selected the dropdown value of MODEM in A2, A44,
A525,
A600, A1100, A1555, and A2399 the associated dropdown appearing in Column
B
(at B2, B44, B525, B600, B1100, B1555, and B2399) would appear without
having
to enter INDIRECT statements singly. With possibly 15 different people
entering data, these "automatic-directed-dropdown" would be invaluable.

LOL, I hope this is clearer!

Thx,
Jeff



T. Valko said:
Not sure what you mean by "carry over" ?

Did you set B40 to use data validation?

--
Biff
Microsoft Excel MVP


Henn9660 said:
Thanks for the info. I already entered the Name Ranges for each of the
39
types.

A couple of days ago, I used "Indirect" to test each of the 39 Hardware
Types and it worked fine.

However, I then went to line A40 and entered one of the previous values
again, only to find that the dropdown for B40 did not 'carry over' the
"Indirect" value from above for the Hardware Type listed at A40. Any
thoughts? Thx, Jeff

:

What you need to do is create named ranges for each of the hardware
types.

For example:

Named range: Modem
Refers to: AE2:AE20

Then, as the source for the drop down in B2 use this formula:

=INDIRECT(A2)



--
Biff
Microsoft Excel MVP


Good Morning Everyone!

I've read all of the 'Contextures Articles' on Dependent Dropdowns,
but
they
do not seem to address what I am specifically trying to accomplish.

I have a 4000-line spreadsheet used to track hardware and
peripherals.
Column A (Category) invokes Data Validation to a list of 39 Hardware
Types.

Column B2, for example, would contain the name of the Manufacturer
for
the
Hardware Type selected in A2 and so on through A4000.

The individual Manufacturer Names [for each of the 39 Hardware
Types]
in
Category are listed in Columns AA through BM. Three of the
Categories
I
use
are Modem, Projector, and Hub.

What I'm looking for (hopefully) would be a way to use a "large" IF
statement that challenges the dropdown values chosen in A2-A4000 to
the
appropriate Manufacturer Names to be dropdown-displayed in
B2...B4000.

The 39 Hardware Types are contained [out of sight] in Columns AA-BM.

What I would like to do is something like:

IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers)
OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector
Manufacturers)
OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers)
OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on
until
OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is
Switch)

Any thoughts would be most greatly appreciated!

Thanks,
Jeff in Mississippi
Click to expand...
Click to expand...
Click to expand...
 
H

Henn9660

I will again apologize... having reread my latest reply. I'm on my way out
for the day (a VERY LONG DAY....) and was wondering if it would be
permissible to contact you directly at comcast or by phone to present my
situation. Thanks, Jeff

T. Valko said:
Sorry, I'm not following you on this.

It sounds like you want the dependent drop down to appear in column B
without having to "define" a source. That's not possible.

--
Biff
Microsoft Excel MVP


Henn9660 said:
I apologize if I am unclear..... (it must be frustration setting in (LOL)).

Let me restate the situation. Column A extends to 4000 lines, max. The
permissible values in Column A (dropdown is called CATEGORY) consist of
1-39
values in the CATEGORY dropdown list. Column B (dropdown is called MFR)
represents the Manufacturer of the Hardware listed in Column A. Each of
the
39 CATEGORY entries has its own unique list of MANUFACTURERS.

(The Category lists contains entries such as VPN, Hub, Modem, PatchPanel,
UPS, Server, etc.)

For example if users selected the dropdown value of MODEM in A2, A44,
A525,
A600, A1100, A1555, and A2399 the associated dropdown appearing in Column
B
(at B2, B44, B525, B600, B1100, B1555, and B2399) would appear without
having
to enter INDIRECT statements singly. With possibly 15 different people
entering data, these "automatic-directed-dropdown" would be invaluable.

LOL, I hope this is clearer!

Thx,
Jeff



T. Valko said:
Not sure what you mean by "carry over" ?

Did you set B40 to use data validation?

--
Biff
Microsoft Excel MVP


Thanks for the info. I already entered the Name Ranges for each of the
39
types.

A couple of days ago, I used "Indirect" to test each of the 39 Hardware
Types and it worked fine.

However, I then went to line A40 and entered one of the previous values
again, only to find that the dropdown for B40 did not 'carry over' the
"Indirect" value from above for the Hardware Type listed at A40. Any
thoughts? Thx, Jeff

:

What you need to do is create named ranges for each of the hardware
types.

For example:

Named range: Modem
Refers to: AE2:AE20

Then, as the source for the drop down in B2 use this formula:

=INDIRECT(A2)



--
Biff
Microsoft Excel MVP


Good Morning Everyone!

I've read all of the 'Contextures Articles' on Dependent Dropdowns,
but
they
do not seem to address what I am specifically trying to accomplish.

I have a 4000-line spreadsheet used to track hardware and
peripherals.
Column A (Category) invokes Data Validation to a list of 39 Hardware
Types.

Column B2, for example, would contain the name of the Manufacturer
for
the
Hardware Type selected in A2 and so on through A4000.

The individual Manufacturer Names [for each of the 39 Hardware
Types]
in
Category are listed in Columns AA through BM. Three of the
Categories
I
use
are Modem, Projector, and Hub.

What I'm looking for (hopefully) would be a way to use a "large" IF
statement that challenges the dropdown values chosen in A2-A4000 to
the
appropriate Manufacturer Names to be dropdown-displayed in
B2...B4000.

The 39 Hardware Types are contained [out of sight] in Columns AA-BM.

What I would like to do is something like:

IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers)
OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector
Manufacturers)
OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers)
OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on
until
OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category is
Switch)

Any thoughts would be most greatly appreciated!

Thanks,
Jeff in Mississippi
Click to expand...
Click to expand...
Click to expand...
 
T

T. Valko

I'm at:

xl can help at comcast period net

Remove can and change the obvious.

--
Biff
Microsoft Excel MVP


Henn9660 said:
I will again apologize... having reread my latest reply. I'm on my way out
for the day (a VERY LONG DAY....) and was wondering if it would be
permissible to contact you directly at comcast or by phone to present my
situation. Thanks, Jeff

T. Valko said:
Sorry, I'm not following you on this.

It sounds like you want the dependent drop down to appear in column B
without having to "define" a source. That's not possible.

--
Biff
Microsoft Excel MVP


Henn9660 said:
I apologize if I am unclear..... (it must be frustration setting in
(LOL)).

Let me restate the situation. Column A extends to 4000 lines, max.
The
permissible values in Column A (dropdown is called CATEGORY) consist of
1-39
values in the CATEGORY dropdown list. Column B (dropdown is called
MFR)
represents the Manufacturer of the Hardware listed in Column A. Each
of
the
39 CATEGORY entries has its own unique list of MANUFACTURERS.

(The Category lists contains entries such as VPN, Hub, Modem,
PatchPanel,
UPS, Server, etc.)

For example if users selected the dropdown value of MODEM in A2, A44,
A525,
A600, A1100, A1555, and A2399 the associated dropdown appearing in
Column
B
(at B2, B44, B525, B600, B1100, B1555, and B2399) would appear without
having
to enter INDIRECT statements singly. With possibly 15 different people
entering data, these "automatic-directed-dropdown" would be invaluable.

LOL, I hope this is clearer!

Thx,
Jeff



:

Not sure what you mean by "carry over" ?

Did you set B40 to use data validation?

--
Biff
Microsoft Excel MVP


Thanks for the info. I already entered the Name Ranges for each of
the
39
types.

A couple of days ago, I used "Indirect" to test each of the 39
Hardware
Types and it worked fine.

However, I then went to line A40 and entered one of the previous
values
again, only to find that the dropdown for B40 did not 'carry over'
the
"Indirect" value from above for the Hardware Type listed at A40.
Any
thoughts? Thx, Jeff

:

What you need to do is create named ranges for each of the hardware
types.

For example:

Named range: Modem
Refers to: AE2:AE20

Then, as the source for the drop down in B2 use this formula:

=INDIRECT(A2)



--
Biff
Microsoft Excel MVP


Good Morning Everyone!

I've read all of the 'Contextures Articles' on Dependent
Dropdowns,
but
they
do not seem to address what I am specifically trying to
accomplish.

I have a 4000-line spreadsheet used to track hardware and
peripherals.
Column A (Category) invokes Data Validation to a list of 39
Hardware
Types.

Column B2, for example, would contain the name of the
Manufacturer
for
the
Hardware Type selected in A2 and so on through A4000.

The individual Manufacturer Names [for each of the 39 Hardware
Types]
in
Category are listed in Columns AA through BM. Three of the
Categories
I
use
are Modem, Projector, and Hub.

What I'm looking for (hopefully) would be a way to use a "large"
IF
statement that challenges the dropdown values chosen in A2-A4000
to
the
appropriate Manufacturer Names to be dropdown-displayed in
B2...B4000.

The 39 Hardware Types are contained [out of sight] in Columns
AA-BM.

What I would like to do is something like:

IF A2:A4000 EQUALS "Modem" GOTO AE2:AE20 (Modem Manufacturers)
OR IF A2:A4000 EQUALS "Projector" GOTO AG2:AG24 (Projector
Manufacturers)
OR IF A2:A4000 EQUALS "Hub" GOTO AJ2:AJ15 (Hub Manufacturers)
OR IF A2:A4000 EQUALS "Server" GOTO BA2:BA65 .........and so on
until
OR IF A2:A4000 EQUALS "Swtich" GOTO BL2:BL43 (our last Category
is
Switch)

Any thoughts would be most greatly appreciated!

Thanks,
Jeff in Mississippi
Click to expand...
Click to expand...
Click to expand...
 
Top