Data Tables, Structured References, and Form Controls

[dan_l]

here's the set up:


I'm using some dummy data via excel query > access to make a dynamic dashboard kinda thing. So the data imports and defaults into a table, which is good because many of the charts can just be pointed at as a structured reference like =table2[salary] and =table2[name]. This works with no problems.


Now, for one particular element, I'm looking to take a dynamic list from [name] and use it to do a few other things. So, I try taking a form control and telling it to get values from =table2[name]. Excel accepts that as a range, but it doesn't work. Nothing populates in the list box and if you check the properties again, I find that the input range is blank.


It doesn't seem to work with data validation either, only doing it with a dv list causes excel to throw a bum reference error.


For the time being I've gone around this with a dynamic named range =offset($a$2,0,0,counta($a$2:$a$50),1), but those can be kind of unreliable when a dynamic dataset is involved, so I've backed it up to delete and rename with a little vba. But, ideally, I'd like to get this working with the table structured reference.


any thoughts?

 

[dan_l]

I should add, I've messed with putting the workbook or worksheet names (separated by a !) but that doesn't seem to work.

 

[cmissal]

Hi Dan,


This was posted a while ago, but I didn't see a solution so I thought I would reply.


I am not sure why a table reference won't work, as you indicated, but if you create a new name for the table reference it works.


I did the following:

1) define a table "Table_Names" with one column header "Name"

2) Populate the table with a few entries.

3) Create a name Name_List and set it to refer to: "=Table_Names[Name]"

4) Add the Form Conrol "Combo Box", format control > Control (tab), set input range to "Name_List" (and other values as appropriate)


This approach allows you to use the table reference you like without any VBA or anything else