×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

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

Drop Down Lists
4

Drop Down Lists

Drop Down Lists

(OP)
As a follow up to linking spreadsheets...

Is it possible to link a dropdown list of the section types for different spreadsheets/data?  I have currently created a new column using the original section designations that are sorted from biggest to smallest section.  I have then sorted balance of the data in the sheet for VLOOKUP to work properly.

Alternatively is it possible to test a value and using the test result to load a different dropdown list?

Dik

RE: Drop Down Lists

Hm...
I have spreadsheets where the drop down list changes according to a prior selection. This is simply to target the spreadsheet control to an array of cells and to populate these cells with another list using IF function.
Is this what you mean?
If so then your test result is entered into a specific cell and the IF function loads the appropriate list according to the test result cell contents.
The only problem I have is that some lists have empty cells and the drop down list then shows blank spaces, but it works.

JMW
www.ViscoAnalyser.com

RE: Drop Down Lists

dik wrote:

"Alternatively is it possible to test a value and using the test result to load a different dropdown list?"

Yes, you can do this by using Names and the INDIRECT function.  If you don't know how to use Names in Excel please review the help file or the FAQ on this forum.  You could do this without using names, but it would be hopelessly confusing to explain and follow along....

Here are the steps:
1.  Go to the ranges of values that constitute your various drop down lists and define a name for each.  For instance, LIST_1 and LIST_2.

2.  You will need to enter a formula for the test that returns the name of one of the lists.  For example "=IF(testvalue>100,"LIST_2","LIST_1")" if the value 100 is the cutoff for the first list.  You can enter this into a cell on the worksheet (typical approach), or directly in the DEFINE NAME dialog (elegant approach).  Name this cell or formula "dropdown_list_to_use" - it is going to tell Excel's data validation the name of the dropdown list you wish to appear.  

Note that at this point the value of "dropdown_list_to_use" will be either "LIST_1" or "LIST_2".  If you have a lot of lists, you might want to use a lookup instead of the IF/THEN function to compute the name of the list to be used.  So long as the value in this cell is a valid, defined name you are good to go.

3.  Now go to the cell where you want the dropdown list to appear.  Go to the menu DATA>Validation>Settings tab and select LIST as the allowable data type.  Then enter =INDIRECT(dropdown_list_to_use) as the source.  Voila, you're done!

You could enter =the_name_of_any_defined_list here, but the useful INDIRECT function allows this list name to be a computed value, based on your test criteria.

I hope this makes sense, good luck.



RE: Drop Down Lists

(OP)
Thanks... I'll give it a shot tonight... Dik

RE: Drop Down Lists

(OP)
Joerd... good sites... thanks, Dik

RE: Drop Down Lists

(OP)
Bitseattle... great advice... you need more stars!

Using Names for either a value or a range works really well... something I'll do in most future spreadsheets. The indirect works well, but has to be done in a 'dummy' cell and referenced... and VLOOKUP works well with the names, and is actually 'readable'...

One little hiccup is that if I select a W section and then select an HSS_R (rectangular HSS) the initial value in the dropdown list shows the original W section and does not show the HSS until I select one.  Is there a manner of clearing the data or updating it to some default?

Dik

RE: Drop Down Lists

If you get into visual basic you may have some imperfect options.

VBA lets you execute macros when specific worksheet events happen, like changing the selection, recalculating, etc.  

You can use the following code attached to the specific worksheet with the dropdown.  The hitch is that the cell will reset every time the worksheet recalcs.  BUT if you strictly restrict the users options to only being able to change the list boxes, by protecting the worksheet and using only unlocking the dropbox cells, maybe this would be okay.

Private Sub Worksheet_Calculate()
  Range("name_of_dropdown_to_reset").Value = ""
End Sub

I think this system of changing dropdowns is probably better suited to using VBA user forms, but that's a lot to take on if you are new to this.  

Hopefully someone else can help out on this one, I'll keep looking as I have time.


RE: Drop Down Lists

(OP)
Not a problem... used to program in VB way back when it first came out... then switched to Delphi... I have several BASIC programs I wrote with over a meg in source...

Is there a manner of combining two formulas for a single cell?

Dik

RE: Drop Down Lists

You can also use the Update event of the first dropdown to reset the cell/dropdown values.

@dik
Do you want two results in one cell? That is not possible, as far as I know. But there are a lot more events that just the Calculate event for a worksheet. Take a look in the VB editor, and pick one that works for you (e.g. Worksheet_Change)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Drop Down Lists

"You can also use the Update event of the first dropdown to reset the cell/dropdown values"

Is this possible on a worksheet or only with VBA forms?

RE: Drop Down Lists

Yes, each control on a worksheet can be accessed via VB. So if you place a combobox control on Sheet1, and press the View Code button on the Controls toolbar, it takes you to the ComboBox1_Change event (assuming you placed ComboBox1 of course). You can pick any other event for that combobox, if you like, in the editor.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Drop Down Lists

Easy way to add drop down list is as follows:

select Data->Validation from menu and in "Setting" Tab select List in the Allow column and in source assign the source.
Excel will create drop down automatically

bye
Razook

RE: Drop Down Lists

(OP)
Thanks razook... the problem that I had was that the information does't automatically update other cells... any suggestions?

Dik

RE: Drop Down Lists

Suppose you have a primary selection drop down in cell a1 added using the data validation feature.  The validation is set up as a list with the source =A2:D2.  Associated with each primary selection you can specify a range of values to appear in a secondary selection drop by setting the allow box to "list" and in source add the following formula:

=CHOOSE(MATCH(A1,A2:D2),A3:A5,B3:B7,C3:C8,D3:D11)

assuming the choices for
A2 is a3:a5
B2 is B3:B7
C3 is C3:C8
D3 is D3:D11


RE: Drop Down Lists

(OP)
Thanks, I'll give it a try... Dik

RE: Drop Down Lists

How do I obtain a spreasheet that lists all the section properties of AISC steel beams? I'd love to incorporate this into my spreadsheets!

RE: Drop Down Lists

Hi, Cummings54,

That is an excellent solution given. Small hitch is it doesnt work if your primary list is unsorted. to overcome this use 0 as third argument for Match function for exact search. Now the source will read
=CHOOSE(MATCH(A1,A2:D2,0),A3:A5,B3:B7,C3:C8,D3:D11)

But still it doesnt update other cells, but it definetly will change the drop down items.

Razook

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