Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations SSS148 on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Drop Down Lists 4

Status
Not open for further replies.

dik

Structural
Apr 13, 2001
26,025
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
 
Replies continue below

Recommended for you

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



 
Thanks... I'll give it a shot tonight... Dik
 
Joerd... good sites... thanks, Dik
 
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
 
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.


 
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
 
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.
 
"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?
 
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.
 
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
 
Thanks razook... the problem that I had was that the information does't automatically update other cells... any suggestions?

Dik
 
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


 
Thanks, I'll give it a try... Dik
 
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!
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor