Excel -- Data Validation -- Create Dependent Lists
 

Hi

Can you create an automatic default value if you choose one option from a
drop down list and a dependant list if you do not.

I have been to the following website
"http://www.contextures.com/xlDataVal02.html"and have managed to create the
dependent lists and if I choose the yes answer then I get one list and if I
choose no then I get another but what I can not seem to do is to get it to
default automatically to one value if I choose no ie.
I have a yes or no drop down and if you answer No then I want it to
automatically assign N/A but if yes is chosen then they need to choose from
the applicable drop down list. At the moment you have to go into the cell and
choose N/A when you pick a No answer

I hope someone get help me

Thanks

Louisa

Excel -- Data Validation -- Create Dependent Lists
 

Louisa
You have a data validation cell with choices of Yes and No. Is that
right? You want that cell to display N/A if the user selects No. Is that
right? If this is right then, yes, you can do that but it will take VBA to
do it. The following macro will do that for you. I assumed that the data
validation cell is A1. Change that in the macro as needed. This is a sheet
macro and must be placed in the sheet module of that sheet. You access that
module by right-clicking on the sheet tab and selecting View Code. Paste
this macro into that module. "X" out of the module to return to your sheet.
When you set up the data validation in that cell, you must select to NOT
display an error message if a wrong value is entered. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
If UCase(Target) = "NO" Then
Application.EnableEvents = False
Target = "N/A"
Application.EnableEvents = True
End If
End If
End Sub
"Louisa" wrote in message
...
Hi

Can you create an automatic default value if you choose one option from a
drop down list and a dependant list if you do not.

I have been to the following website
"http://www.contextures.com/xlDataVal02.html"and have managed to create
the
dependent lists and if I choose the yes answer then I get one list and if
I
choose no then I get another but what I can not seem to do is to get it to
default automatically to one value if I choose no ie.
I have a yes or no drop down and if you answer No then I want it to
automatically assign N/A but if yes is chosen then they need to choose
from
the applicable drop down list. At the moment you have to go into the cell
and
choose N/A when you pick a No answer

I hope someone get help me

Thanks

Louisa

Excel -- Data Validation -- Create Dependent Lists
 

Hi Thanks so much for the quick response below you are a star.

I have however realised that I have made a mistake with my question so let
me explain better.

In cell A2 I have a list of Yes or No, if you choose Yes then cell B2 has a
drop down list of options, if you choose No then I want the cell B2 to
default to N/A.

I want to copy this formula over a range of cells in the spreadsheet.
I am currently using a work around with an IF function but if you can think
of a better way I would appreciate it

Thanks again

Louisa

"Otto Moehrbach" wrote:

Louisa
You have a data validation cell with choices of Yes and No. Is that
right? You want that cell to display N/A if the user selects No. Is that
right? If this is right then, yes, you can do that but it will take VBA to
do it. The following macro will do that for you. I assumed that the data
validation cell is A1. Change that in the macro as needed. This is a sheet
macro and must be placed in the sheet module of that sheet. You access that
module by right-clicking on the sheet tab and selecting View Code. Paste
this macro into that module. "X" out of the module to return to your sheet.
When you set up the data validation in that cell, you must select to NOT
display an error message if a wrong value is entered. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
If UCase(Target) = "NO" Then
Application.EnableEvents = False
Target = "N/A"
Application.EnableEvents = True
End If
End If
End Sub
"Louisa" wrote in message
...
Hi

Can you create an automatic default value if you choose one option from a
drop down list and a dependant list if you do not.

I have been to the following website
"http://www.contextures.com/xlDataVal02.html"and have managed to create
the
dependent lists and if I choose the yes answer then I get one list and if
I
choose no then I get another but what I can not seem to do is to get it to
default automatically to one value if I choose no ie.
I have a yes or no drop down and if you answer No then I want it to
automatically assign N/A but if yes is chosen then they need to choose
from
the applicable drop down list. At the moment you have to go into the cell
and
choose N/A when you pick a No answer

I hope someone get help me

Thanks

Louisa

Excel -- Data Validation -- Create Dependent Lists
 

Louisa
I changed the macro a little to show "N/A" in Column B if "No" is
selected in Column A. You can setup Data Validation in A2 and B2 like you
want. Remember to setup the Data Validation in B2 to NOT display an error
if a wrong value (not in the list) is entered. You can then copy those 2
cells as far down as you want. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing And _
Target.Row 1 Then
If UCase(Target) = "NO" Then
Application.EnableEvents = False
Target.Offset(, 1) = "N/A"
Application.EnableEvents = True
End If
End If
End Sub
"Louisa" wrote in message
...
Hi Thanks so much for the quick response below you are a star.

I have however realised that I have made a mistake with my question so let
me explain better.

In cell A2 I have a list of Yes or No, if you choose Yes then cell B2 has
a
drop down list of options, if you choose No then I want the cell B2 to
default to N/A.

I want to copy this formula over a range of cells in the spreadsheet.
I am currently using a work around with an IF function but if you can
think
of a better way I would appreciate it

Thanks again

Louisa

"Otto Moehrbach" wrote:

Louisa
You have a data validation cell with choices of Yes and No. Is that
right? You want that cell to display N/A if the user selects No. Is
that
right? If this is right then, yes, you can do that but it will take VBA
to
do it. The following macro will do that for you. I assumed that the
data
validation cell is A1. Change that in the macro as needed. This is a
sheet
macro and must be placed in the sheet module of that sheet. You access
that
module by right-clicking on the sheet tab and selecting View Code. Paste
this macro into that module. "X" out of the module to return to your
sheet.
When you set up the data validation in that cell, you must select to NOT
display an error message if a wrong value is entered. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
If UCase(Target) = "NO" Then
Application.EnableEvents = False
Target = "N/A"
Application.EnableEvents = True
End If
End If
End Sub
"Louisa" wrote in message
...
Hi

Can you create an automatic default value if you choose one option from
a
drop down list and a dependant list if you do not.

I have been to the following website
"http://www.contextures.com/xlDataVal02.html"and have managed to create
the
dependent lists and if I choose the yes answer then I get one list and
if
I
choose no then I get another but what I can not seem to do is to get it
to
default automatically to one value if I choose no ie.
I have a yes or no drop down and if you answer No then I want it to
automatically assign N/A but if yes is chosen then they need to choose
from
the applicable drop down list. At the moment you have to go into the
cell
and
choose N/A when you pick a No answer

I hope someone get help me

Thanks

Louisa

Excel -- Data Validation -- Create Dependent Lists
 

Hi Otto

THANK YOU SO MUCH ...... it works perfectly ;0). It is so refreshing to come
across a community of people that are so willing to help out with problems

All the best

Louisa

"Otto Moehrbach" wrote:

Louisa
I changed the macro a little to show "N/A" in Column B if "No" is
selected in Column A. You can setup Data Validation in A2 and B2 like you
want. Remember to setup the Data Validation in B2 to NOT display an error
if a wrong value (not in the list) is entered. You can then copy those 2
cells as far down as you want. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing And _
Target.Row 1 Then
If UCase(Target) = "NO" Then
Application.EnableEvents = False
Target.Offset(, 1) = "N/A"
Application.EnableEvents = True
End If
End If
End Sub
"Louisa" wrote in message
...
Hi Thanks so much for the quick response below you are a star.

I have however realised that I have made a mistake with my question so let
me explain better.

In cell A2 I have a list of Yes or No, if you choose Yes then cell B2 has
a
drop down list of options, if you choose No then I want the cell B2 to
default to N/A.

I want to copy this formula over a range of cells in the spreadsheet.
I am currently using a work around with an IF function but if you can
think
of a better way I would appreciate it

Thanks again

Louisa

"Otto Moehrbach" wrote:

Louisa
You have a data validation cell with choices of Yes and No. Is that
right? You want that cell to display N/A if the user selects No. Is
that
right? If this is right then, yes, you can do that but it will take VBA
to
do it. The following macro will do that for you. I assumed that the
data
validation cell is A1. Change that in the macro as needed. This is a
sheet
macro and must be placed in the sheet module of that sheet. You access
that
module by right-clicking on the sheet tab and selecting View Code. Paste
this macro into that module. "X" out of the module to return to your
sheet.
When you set up the data validation in that cell, you must select to NOT
display an error message if a wrong value is entered. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
If UCase(Target) = "NO" Then
Application.EnableEvents = False
Target = "N/A"
Application.EnableEvents = True
End If
End If
End Sub
"Louisa" wrote in message
...
Hi

Can you create an automatic default value if you choose one option from
a
drop down list and a dependant list if you do not.

I have been to the following website
"http://www.contextures.com/xlDataVal02.html"and have managed to create
the
dependent lists and if I choose the yes answer then I get one list and
if
I
choose no then I get another but what I can not seem to do is to get it
to
default automatically to one value if I choose no ie.
I have a yes or no drop down and if you answer No then I want it to
automatically assign N/A but if yes is chosen then they need to choose
from
the applicable drop down list. At the moment you have to go into the
cell
and
choose N/A when you pick a No answer

I hope someone get help me

Thanks

Louisa

Excel -- Data Validation -- Create Dependent Lists
 

Glad to help. Thanks for the feedback. Otto
"Louisa" wrote in message
...
Hi Otto

THANK YOU SO MUCH ...... it works perfectly ;0). It is so refreshing to
come
across a community of people that are so willing to help out with problems

All the best

Louisa

"Otto Moehrbach" wrote:

Louisa
I changed the macro a little to show "N/A" in Column B if "No" is
selected in Column A. You can setup Data Validation in A2 and B2 like
you
want. Remember to setup the Data Validation in B2 to NOT display an
error
if a wrong value (not in the list) is entered. You can then copy those
2
cells as far down as you want. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing And _
Target.Row 1 Then
If UCase(Target) = "NO" Then
Application.EnableEvents = False
Target.Offset(, 1) = "N/A"
Application.EnableEvents = True
End If
End If
End Sub
"Louisa" wrote in message
...
Hi Thanks so much for the quick response below you are a star.

I have however realised that I have made a mistake with my question so
let
me explain better.

In cell A2 I have a list of Yes or No, if you choose Yes then cell B2
has
a
drop down list of options, if you choose No then I want the cell B2 to
default to N/A.

I want to copy this formula over a range of cells in the spreadsheet.
I am currently using a work around with an IF function but if you can
think
of a better way I would appreciate it

Thanks again

Louisa

"Otto Moehrbach" wrote:

Louisa
You have a data validation cell with choices of Yes and No. Is
that
right? You want that cell to display N/A if the user selects No. Is
that
right? If this is right then, yes, you can do that but it will take
VBA
to
do it. The following macro will do that for you. I assumed that the
data
validation cell is A1. Change that in the macro as needed. This is a
sheet
macro and must be placed in the sheet module of that sheet. You
access
that
module by right-clicking on the sheet tab and selecting View Code.
Paste
this macro into that module. "X" out of the module to return to your
sheet.
When you set up the data validation in that cell, you must select to
NOT
display an error message if a wrong value is entered. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
If UCase(Target) = "NO" Then
Application.EnableEvents = False
Target = "N/A"
Application.EnableEvents = True
End If
End If
End Sub
"Louisa" wrote in message
...
Hi

Can you create an automatic default value if you choose one option
from
a
drop down list and a dependant list if you do not.

I have been to the following website
"http://www.contextures.com/xlDataVal02.html"and have managed to
create
the
dependent lists and if I choose the yes answer then I get one list
and
if
I
choose no then I get another but what I can not seem to do is to get
it
to
default automatically to one value if I choose no ie.
I have a yes or no drop down and if you answer No then I want it to
automatically assign N/A but if yes is chosen then they need to
choose
from
the applicable drop down list. At the moment you have to go into the
cell
and
choose N/A when you pick a No answer

I hope someone get help me

Thanks

Louisa

Excel -- Data Validation -- Create Dependent Lists
 

You wouldn't happen to have the "resizeable image pasting" code that
does not stack images, but deletes the previous before pasting the new,
and resizing it?

It is a variant of the one on that guy's help page.

Dang... I guess I could spend some time searching the archive for the
code I know is already posted there... ;-]

Nevermind... I'll get to work hunting. :-]


On Thu, 8 Oct 2009 22:08:08 -0400, "Otto Moehrbach"
wrote:

Glad to help. Thanks for the feedback. Otto
"Louisa" wrote in message
...
Hi Otto

THANK YOU SO MUCH ...... it works perfectly ;0). It is so refreshing to
come
across a community of people that are so willing to help out with problems

All the best

Louisa

"Otto Moehrbach" wrote:

Louisa
I changed the macro a little to show "N/A" in Column B if "No" is
selected in Column A. You can setup Data Validation in A2 and B2 like
you
want. Remember to setup the Data Validation in B2 to NOT display an
error
if a wrong value (not in the list) is entered. You can then copy those
2
cells as far down as you want. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing And _
Target.Row 1 Then
If UCase(Target) = "NO" Then
Application.EnableEvents = False
Target.Offset(, 1) = "N/A"
Application.EnableEvents = True
End If
End If
End Sub
"Louisa" wrote in message
...
Hi Thanks so much for the quick response below you are a star.

I have however realised that I have made a mistake with my question so
let
me explain better.

In cell A2 I have a list of Yes or No, if you choose Yes then cell B2
has
a
drop down list of options, if you choose No then I want the cell B2 to
default to N/A.

I want to copy this formula over a range of cells in the spreadsheet.
I am currently using a work around with an IF function but if you can
think
of a better way I would appreciate it

Thanks again

Louisa

"Otto Moehrbach" wrote:

Louisa
You have a data validation cell with choices of Yes and No. Is
that
right? You want that cell to display N/A if the user selects No. Is
that
right? If this is right then, yes, you can do that but it will take
VBA
to
do it. The following macro will do that for you. I assumed that the
data
validation cell is A1. Change that in the macro as needed. This is a
sheet
macro and must be placed in the sheet module of that sheet. You
access
that
module by right-clicking on the sheet tab and selecting View Code.
Paste
this macro into that module. "X" out of the module to return to your
sheet.
When you set up the data validation in that cell, you must select to
NOT
display an error message if a wrong value is entered. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
If UCase(Target) = "NO" Then
Application.EnableEvents = False
Target = "N/A"
Application.EnableEvents = True
End If
End If
End Sub
"Louisa" wrote in message
...
Hi

Can you create an automatic default value if you choose one option
from
a
drop down list and a dependant list if you do not.

I have been to the following website
"http://www.contextures.com/xlDataVal02.html"and have managed to
create
the
dependent lists and if I choose the yes answer then I get one list
and
if
I
choose no then I get another but what I can not seem to do is to get
it
to
default automatically to one value if I choose no ie.
I have a yes or no drop down and if you answer No then I want it to
automatically assign N/A but if yes is chosen then they need to
choose
from
the applicable drop down list. At the moment you have to go into the
cell
and
choose N/A when you pick a No answer

I hope someone get help me

Thanks

Louisa