×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Data Validation

thread770-389100

Data Validation

Data Validation

(OP)
Hello Everyone,

I am fairly new to using drop down menus in Excel, but I have managed to get a few working and have them linked so the drop down menu is dependent on an initial selection. For example, you can see the webpage here I am using to make the conditional lists.... -> http://blogs.office.com/2009/11/24/create-conditio...

My only issue is that if I make an initial section in A1, which gives me a drop down menu in B1 based on the selection in A1. After I make the selection in B1, if I go back and change A1, it is possible that I am left with an "impossible" combination. I believe this is what was being discussed in the older thread below.

thread770-165533: Drop Down Lists

I'm wondering if it is possible in the newer versions in Excel to automatically change or clear the selection in B1 if A1 is changed after.

I hope that makes sense. I can provide screenshots if I need to provide more clarification.


Cheers,
Macmet

RE: Data Validation

Hi,

Resetting the dependent DropDown is something you must do with your sheet, via Event code that behaves: when A1 changes, reset all dependent DropDowns. This can be accomplished in VBA in that sheet's VBA Code module in the WorkSheet_Change event.

How is your Data Validation LIST referenced in your dependent DropDown?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data Validation

(OP)
Skip,

I appreciate the feedback. I am on the road with the spreadsheet I was using. When I get back to the office I will post more details.

Thank you


Macmet

RE: Data Validation

Standing by.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data Validation

(OP)
Wow, I cannot believe this been a month. But I am back using the sheet again.

I am using the Indirect function in my dependent pull down menu.

For example I have my first pulldown menu in C9 with the reference to my TASKS list.

The second pull down menu is in E9 I have,

=INDIRECT($C$9)

Then then I also have another pulldown in F9 where it is,

=INDIRECT($E$9)

This works when I go through and make the selections in the proper order. If I want to go back and change the selection picked in the list in C9, the selections made in E9, F9 stay the same. What would be great would be any time I changed the selection in C9, the cells in E9, F9 would go blank. And also if I were to change the selection in E9 only, the F9 cell would go blank.

I hope that makes sense.


Macmet

RE: Data Validation

You're going to have to use VBA to do this. This is done with Change Event in the Excel sheet.

Here's the way to find these Events:

1) Right-click the Sheet TAB and select View Code. This exposes the Code Sheet in the VBA Editor for this Sheet. (Alt+F11 will toggle between the Sheet and the VBA Editor.)
2) Just above and on the left-hand side, is the Object Drop Down. You'll see (General) initially. Click the Drop Down and select Worksheet.
3) Just above and on the right-hand side, is the Procedure Drop Down. You'll see Selection Change initially. Click the Drop Down and select Change, because we want to do something when some value changes in C9, your initial Drop Down.

This is what you'll see in the Code Sheet...

CODE

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
So here's what we need to happen: When you make a new selection, ie change the value in C9, the contents of E9 & F9 need to be cleared.

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Cells(9, "C")) is nothing Then
      Cells(9, "E").ClearContents
      Cells(9, "F").ClearContents
   End If 
End Sub
So although this event fires any time ANYTHING changes value in this sheet, the ClesrContents only occurs when The Target coincides with C9.


Give the E9 change a try yourself and see if you can make that work. Post back if you need more help.

Hope that works for you.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data Validation

(OP)
Skip,

That works exactly how I wanted it.

Does that code become part of this spreadsheet? I.e. if I send it to my colleagues, will the ClearContents work for them or do they need to do that VBA code?

Thanks,
Macmet

RE: Data Validation

It is in the sheet. However, they will need to Enable Macros to get this functionality.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login

Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now
Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close