Excel -- Data Validation -- Create Dependent Lists

[Louisa]

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

 

[Otto Moehrbach]

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]

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]

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]

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]

Glad to help. Thanks for the feedback. Otto

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

 

[FatBytestard]

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. :-]