Help w/dynamic table names and drop down lists.

Question

Hi Isaac.

I thought I was an advanceduser, but this one has me stumped. I hope you can help me:

I have several data tables, which I've labeled say Table1, Table2,...Table 50. and can see them listed in the Name Manager box of Excel2010. I've also defined two new names called DataToUse-A and DataToUse-B, and have three defined drop-down boxes for the user to make a selection for both data A & B (DropDown1, 2, 3). So once a user makes their 3 drop-down selections for category A, then I need to set DataToUse-A to the correct table (e.g. Table23), and the same for when they've made their 3 drop-down selections for category B, then set DataToUse-B to the correct table (e.g. Table37). And then anytime the user would change one of the drop-down boxes in either category A or B, the corresponding DatToUse-A or -B value would be dynamically updated as well.

Not sure if this is an""calculation"" or ""formula"" or possibly requires a macro. I hope NOT a macro. Thought you would be able to help me.

Please let me know how I can accomplish this. I have an older example I can email you if you like, but that sheet is ""locked"" and I can't see the background data, formulas, etc.

for your help. Very much appreciated.

-James.

Answer

I am advanced in some areas, beginner in others -is a never ending world of possibilities and I've just begun delving into it - as all folks find out- the more we think we know, the more we realize we need to know more.

I am not 100% sure I'm understanding your question, but perhaps you are talking about cascading validation?

If so, to this day I still use this method described here, credit to Martin at utteraccess.com

http://www.utteraccess.com/forum/Cascaded-Validation-Lists-t1761212.html

You may have to tweak it a little to meet your "table" set up. I'm not really aware of any feature inthat's truly called a Table (except pivot tables), but I know people often use the term to simply refer to a range of data, and often a Named Range.

Question

Hi Damon.

I thought I was an advanceduser, but this one has me stumped. I hope you can help me:

I have several data tables, which I've labeled say Table1, Table2,...Table 50. and can see them listed in the Name Manager box of Excel2010. I've also defined two new names called DataToUse-A and DataToUse-B, and have three defined drop-down boxes for the user to make a selection for both data A & B (DropDown1, 2, 3). So once a user makes their 3 drop-down selections for category A, then I need to set DataToUse-A to the correct table (e.g. Table23), and the same for when they've made their 3 drop-down selections for category B, then set DataToUse-B to the correct table (e.g. Table37). And then anytime the user would change one of the drop-down boxes in either category A or B, the corresponding DatToUse-A or -B value would be dynamically updated as well.

Not sure if this is an"calculation" or "formula" or possibly requires a macro. I hope NOT a macro. Thought you would be able to help me.

Please let me know how I can accomplish this. I have an older example I can email you if you like, but that sheet is "locked" and I can't see the background data, formulas, etc.

for your help. Very much appreciated.

-James.

Answer

You didn't mention which kind of dropdown you are using (whether a Forms control dropdown or an ActiveX combobox dropdown), so I am going to assume it is the newer ActiveX combobox dropdown.

Strictly speaking you don't have to use a macro for this, but you do have to add VBA code to the dropdown control's Change event. You will still have to enable macros for this code to be able to run. The code you will have to add will look something like this:

Names("DataToUse-A").RefersTo = Names("Table23").RefersTo

This causes both names to refer to the same cell range. To get to the Change event code for a dropdown in the Visual Basic Editor, simply double-click on the control while in the Design mode.

Since I don't know how you use the various dropdown selections to determine which table to assign the two DataToUse names to I can't help with that code, but you will need your code to produce the desired table ("Table23") as a text string, and pass it to the line of code shown above.

I hope you find this helpful, but if all this is confusing, feel free to email me the workbook along with some description how the 3 dropdown selections map into a table assignment for each of the two DataToUse ranges. My email is vbaexpert-AT-myway.com (replace -AT- with @).