I have one ActiveX combobox (combobox1) and a table with 3 columns of data (Table1 [Col1],[Col2],[Col2]).
What I'm trying to do is display the whole table, or at least two of the columns (without heading) in the dropdown list.
The best I could do is type the existing data range, A2:C30 (first row being the heading), into the ListFillRange property. That worked, but the range was static, not dynamic.
What I have tried so far:
1. Defined a named range, using OFFSET(A2,0,0,COUNTA($A:$A)-1,3)), which included all three columns -- Rejected.
2. Typed the table name [Table1] in the ListFillRange property -- Rejected.
3. Defined three dynamic ranges, each representing a column in Table1, and entered Column1:Column3 in the ListFillRange property -- Rejected.
4. Typed Table1[#Data] into ListFillRange -- Rejected.
In short, nothing worked unless I specifically circled out the cell range. Single-column dynamic range sources worked, but not multi-column.
So what other options do I have? No VBA allowed here, or else I would have done it already.
There's a second part to this question, but I'll leave that for later.
Bookmarks