Dependent Dropdown Lists - Another Question

Dougie1

Dougie1

Board Regular
Joined
Jul 27, 2007
Messages
212
Hi all,

Using Excel 2003

I hope somebody can help me with another question on dependent dropdowns.

I have a spreadsheet which uses the methodology of Data Validation in column B to allow a List and sets the Data Validation Source as =INDIRECT(“L”&A2) where L refers to my possible selection of lists for column B (these lists are called L1, L2, L3, L4 etc and have been defined as such). This then generates a list of options in the cell B2 dropdown list dependent on what the value is in A2. This A2 value has also been selected from a list.

Basically, this has the effect of restricting what the user options are in column B dependent on what has been selected as an option in column A. Now this works fine.

But what I have come to realise is that a user can select an option from the list in column A, then go onto select an option from column B (these options in column B are now restricted by their column A selection), but after they have selected their column B option, they can simply then go back and change the column A option, and I will be left with a combination in columns A & B which I don't want.

I would like to know if there is a way that I can prevent this from happening (or at least generate a warning message) if the column B selection does not relate to the correct column A selection.

Thanks in advance,

D
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
You could use another cell to indicate that the combination is unacceptable. If you are doing calculations based on the selections, you can also ensure they are invalidated. Adapt the ideas at
Validate Mandatory Data
http://www.tushar-mehta.com/excel/newsgroups/validate_data/index.html

Also, it may -- and I emphasize may -- be possible to VBA to blank out column B whenever column A changes. But, I use code only as a last resort.

You may also want to look at
Cascading queries
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html
 
Last edited:
Upvote 0
Hi D,

To check this using VBA :

Copy this code to a module

Code: 
Public sFirst As String

.. and this code into the object of the worksheet

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A2")) Is Nothing Then
        If Target.Value <> sFirst Then
            Range("B2").Select
            Selection.ClearContents
            Exit Sub
        End If
    End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("A2")) Is Nothing Then
        sFirst = Target.Value
    End If

End Sub

Erik
 
Upvote 0
Thanks Erik.

I hope you can bare with me as I am not so hot on things VB at all.

I think I understand when you say copy to a module that this code will go in a new module in the VB editor yeah ??

As for the second part - I am not sure how to access the object of the worksheet to copy the second part of code to - how do I do this - can you help ??

Thanks,

D
 
Upvote 0
D,

You're right about the module part, the worksheet object is in the VB editor too. Normally represented with 'Worksheet1', 'Worksheet2', ...

Erik
 
Upvote 0
Hi WinteE,

Can't get that to work either !!

Is there anyone else out there that can suggest how I can easily clear the contents of the dependent list cells (column B) when the selection in the first list cells is changed.

Thanks in advance,

D
 
Upvote 0
Hi Erik,

Just posting to say that after some trial and error I eventually sussed out how to use your code – and it does exactly what I want it to. I was just getting confused over where the 2 separate parts of code went.

This is a great site and I never cease to be amazed by all the functionality that Excel has and the great advice available from more experienced users.

Thanks again for your input (and tusharm too)


D
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
495
MissDB101
M
S
  • Solved
Data validation based on Sheet Name
Replies
5
Views
266
SuperX7
S
C
  • Question
Dependent Drop Down Help, Excel 2010
Replies
5
Views
149
Akuini
Akuini
A
  • Question
Show Data Validation Lists in Next Row of Table
Replies
0
Views
437
AndyPandy27
A
D
  • Question
Create Dependent Check Box Selection
Replies
2
Views
70
Dokat
D

Forum statistics

Threads
1,214,998
Messages
6,122,638
Members
449,093
Latest member
Ahmad123098

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top