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
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
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
"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
RE: Drop Down Lists
and
http://www.ozgrid.com/Excel/DynamicRanges.htm
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Drop Down Lists
RE: Drop Down Lists
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
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
Is there a manner of combining two formulas for a single cell?
Dik
RE: Drop Down Lists
@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
Is this possible on a worksheet or only with VBA forms?
RE: Drop Down Lists
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Drop Down Lists
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
Dik
RE: Drop Down Lists
=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
RE: Drop Down Lists
RE: Drop Down Lists
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