I am working on building a spreadsheet which populates values in cells based
on values in previous cells. All of this is in drop down lists (data
validation list) or using a combo box.
For Example:
if I enter Automobile in A2, I want B2 to be restricted to Ford, GM, BMW,
etc.
if I enter Airline in A2, I want B2 to only show Delta, Northwest, etc.
And so on....
I also want to copy these lists through out the sheet (row 2 - row 100).
I have got to the point where I am using a combo box for the values in A2
but I can not get over the hump to restrict values in B2 to certain values
(Ford, Delta, etc.) based on what was input in A2.

Not sure if that makes sense, but can anyone help? Thanks in advance.

Hi,
You will find a full explanation with sample code here on how to
generate related lists:

http://www.xldynamic.com/source/xld.Dropdowns.html

HTH

"AT" wrote:

I am working on building a spreadsheet which populates values in cells based
on values in previous cells. All of this is in drop down lists (data
validation list) or using a combo box.
For Example:
if I enter Automobile in A2, I want B2 to be restricted to Ford, GM, BMW,
etc.
if I enter Airline in A2, I want B2 to only show Delta, Northwest, etc.
And so on....
I also want to copy these lists through out the sheet (row 2 - row 100).
I have got to the point where I am using a combo box for the values in A2
but I can not get over the hump to restrict values in B2 to certain values
(Ford, Delta, etc.) based on what was input in A2.

Not sure if that makes sense, but can anyone help? Thanks in advance.

Hi Toppers:

Is there any way to use just logical If statements to do this? Also, when I
download the worksheet on the site I get run time errors with the data
validation example. The reason I ask is that this same information has to be
repeated multiple times on a data entry spreadsheet. I am not using a form,
just a manual data entry worksheet.

Any tthoughts? Thanks.

"Toppers" wrote:

Hi,
You will find a full explanation with sample code here on how to
generate related lists:

http://www.xldynamic.com/source/xld.Dropdowns.html

HTH

"AT" wrote:

I am working on building a spreadsheet which populates values in cells based
on values in previous cells. All of this is in drop down lists (data
validation list) or using a combo box.
For Example:
if I enter Automobile in A2, I want B2 to be restricted to Ford, GM, BMW,
etc.
if I enter Airline in A2, I want B2 to only show Delta, Northwest, etc.
And so on....
I also want to copy these lists through out the sheet (row 2 - row 100).
I have got to the point where I am using a combo box for the values in A2
but I can not get over the hump to restrict values in B2 to certain values
(Ford, Delta, etc.) based on what was input in A2.

Not sure if that makes sense, but can anyone help? Thanks in advance.

The gist of the code is:

Capture the change event for cell A2 (worksheet module - change event)

Build a case select or If...Then...Elseif...End If.
Compare the value of A2 against a list.
A Vlookup usually works. Where column 1 of the lookup range contains the
list for Range A2
the 2 nd column contains the names of the other lists that correspond to the
values in A.
Use the result to asign the proper list name to the Data Validation in B2

(this code is just a sample and has not been tested)

dim lst1 as Sting, lst2 as String

If target.address = $A$2 then
lst1 = VLookup(target, Sheets("Tables").Range("A1:B25"),2,False)
End If

Now build code to change the Data Validation list of B2 = lst1
(you can get this code by building the Data Validation with the Recorder on)
--
steveB

Remove "AYN" from email to respond
"AT" wrote in message
...
Hi Toppers:

Is there any way to use just logical If statements to do this? Also, when
I
download the worksheet on the site I get run time errors with the data
validation example. The reason I ask is that this same information has to
be
repeated multiple times on a data entry spreadsheet. I am not using a
form,
just a manual data entry worksheet.

Any tthoughts? Thanks.

"Toppers" wrote:

Hi,
You will find a full explanation with sample code here on how to
generate related lists:

http://www.xldynamic.com/source/xld.Dropdowns.html

HTH

"AT" wrote:

I am working on building a spreadsheet which populates values in cells
based
on values in previous cells. All of this is in drop down lists (data
validation list) or using a combo box.
For Example:
if I enter Automobile in A2, I want B2 to be restricted to Ford, GM,
BMW,
etc.
if I enter Airline in A2, I want B2 to only show Delta, Northwest, etc.
And so on....
I also want to copy these lists through out the sheet (row 2 - row
100).
I have got to the point where I am using a combo box for the values in
A2
but I can not get over the hump to restrict values in B2 to certain
values
(Ford, Delta, etc.) based on what was input in A2.

Not sure if that makes sense, but can anyone help? Thanks in advance.

Hi Steve:

Thanks for your input. I am a novice at VBA and Excel so if you could break
this down a little bit for me I would appreciate it. If not, I understand.
Thanks for your time.
-AT

"STEVE BELL" wrote:

The gist of the code is:

Capture the change event for cell A2 (worksheet module - change event)

Build a case select or If...Then...Elseif...End If.
Compare the value of A2 against a list.
A Vlookup usually works. Where column 1 of the lookup range contains the
list for Range A2
the 2 nd column contains the names of the other lists that correspond to the
values in A.
Use the result to asign the proper list name to the Data Validation in B2

(this code is just a sample and has not been tested)

dim lst1 as Sting, lst2 as String

If target.address = $A$2 then
lst1 = VLookup(target, Sheets("Tables").Range("A1:B25"),2,False)
End If

Now build code to change the Data Validation list of B2 = lst1
(you can get this code by building the Data Validation with the Recorder on)
--
steveB

Remove "AYN" from email to respond
"AT" wrote in message
...
Hi Toppers:

Is there any way to use just logical If statements to do this? Also, when
I
download the worksheet on the site I get run time errors with the data
validation example. The reason I ask is that this same information has to
be
repeated multiple times on a data entry spreadsheet. I am not using a
form,
just a manual data entry worksheet.

Any tthoughts? Thanks.

"Toppers" wrote:

Hi,
You will find a full explanation with sample code here on how to
generate related lists:

http://www.xldynamic.com/source/xld.Dropdowns.html

HTH

"AT" wrote:

I am working on building a spreadsheet which populates values in cells
based
on values in previous cells. All of this is in drop down lists (data
validation list) or using a combo box.
For Example:
if I enter Automobile in A2, I want B2 to be restricted to Ford, GM,
BMW,
etc.
if I enter Airline in A2, I want B2 to only show Delta, Northwest, etc.
And so on....
I also want to copy these lists through out the sheet (row 2 - row
100).
I have got to the point where I am using a combo box for the values in
A2
but I can not get over the hump to restrict values in B2 to certain
values
(Ford, Delta, etc.) based on what was input in A2.

Not sure if that makes sense, but can anyone help? Thanks in advance.

Hi,
A further source (requires no progamming) with sample download:

http://www.contextures.com/xlDataVal02.html

I have used this technique myself.

HTH

"STEVE BELL" wrote:

The gist of the code is:

Capture the change event for cell A2 (worksheet module - change event)

Build a case select or If...Then...Elseif...End If.
Compare the value of A2 against a list.
A Vlookup usually works. Where column 1 of the lookup range contains the
list for Range A2
the 2 nd column contains the names of the other lists that correspond to the
values in A.
Use the result to asign the proper list name to the Data Validation in B2

(this code is just a sample and has not been tested)

dim lst1 as Sting, lst2 as String

If target.address = $A$2 then
lst1 = VLookup(target, Sheets("Tables").Range("A1:B25"),2,False)
End If

Now build code to change the Data Validation list of B2 = lst1
(you can get this code by building the Data Validation with the Recorder on)
--
steveB

Remove "AYN" from email to respond
"AT" wrote in message
...
Hi Toppers:

Is there any way to use just logical If statements to do this? Also, when
I
download the worksheet on the site I get run time errors with the data
validation example. The reason I ask is that this same information has to
be
repeated multiple times on a data entry spreadsheet. I am not using a
form,
just a manual data entry worksheet.

Any tthoughts? Thanks.

"Toppers" wrote:

Hi,
You will find a full explanation with sample code here on how to
generate related lists:

http://www.xldynamic.com/source/xld.Dropdowns.html

HTH

"AT" wrote:

I am working on building a spreadsheet which populates values in cells
based
on values in previous cells. All of this is in drop down lists (data
validation list) or using a combo box.
For Example:
if I enter Automobile in A2, I want B2 to be restricted to Ford, GM,
BMW,
etc.
if I enter Airline in A2, I want B2 to only show Delta, Northwest, etc.
And so on....
I also want to copy these lists through out the sheet (row 2 - row
100).
I have got to the point where I am using a combo box for the values in
A2
but I can not get over the hump to restrict values in B2 to certain
values
(Ford, Delta, etc.) based on what was input in A2.

Not sure if that makes sense, but can anyone help? Thanks in advance.

Hi Toppers,
This works great! Thanks a ton!
-AT

"Toppers" wrote:

Hi,
A further source (requires no progamming) with sample download:

http://www.contextures.com/xlDataVal02.html

I have used this technique myself.

HTH

"STEVE BELL" wrote:

The gist of the code is:

Capture the change event for cell A2 (worksheet module - change event)

Build a case select or If...Then...Elseif...End If.
Compare the value of A2 against a list.
A Vlookup usually works. Where column 1 of the lookup range contains the
list for Range A2
the 2 nd column contains the names of the other lists that correspond to the
values in A.
Use the result to asign the proper list name to the Data Validation in B2

(this code is just a sample and has not been tested)

dim lst1 as Sting, lst2 as String

If target.address = $A$2 then
lst1 = VLookup(target, Sheets("Tables").Range("A1:B25"),2,False)
End If

Now build code to change the Data Validation list of B2 = lst1
(you can get this code by building the Data Validation with the Recorder on)
--
steveB

Remove "AYN" from email to respond
"AT" wrote in message
...
Hi Toppers:

Is there any way to use just logical If statements to do this? Also, when
I
download the worksheet on the site I get run time errors with the data
validation example. The reason I ask is that this same information has to
be
repeated multiple times on a data entry spreadsheet. I am not using a
form,
just a manual data entry worksheet.

Any tthoughts? Thanks.

"Toppers" wrote:

Hi,
You will find a full explanation with sample code here on how to
generate related lists:

http://www.xldynamic.com/source/xld.Dropdowns.html

HTH

"AT" wrote:

I am working on building a spreadsheet which populates values in cells
based
on values in previous cells. All of this is in drop down lists (data
validation list) or using a combo box.
For Example:
if I enter Automobile in A2, I want B2 to be restricted to Ford, GM,
BMW,
etc.
if I enter Airline in A2, I want B2 to only show Delta, Northwest, etc.
And so on....
I also want to copy these lists through out the sheet (row 2 - row
100).
I have got to the point where I am using a combo box for the values in
A2
but I can not get over the hump to restrict values in B2 to certain
values
(Ford, Delta, etc.) based on what was input in A2.

Not sure if that makes sense, but can anyone help? Thanks in advance.