I asked this in StackOverflow and was redirected here. Apologies for redundancy.

I have an Excel worksheet with a combo box on Sheet1 that is populated via its Input Range property from a Dynamic Named Range on Sheet2. It works fine and no VBA is required.

My data on Sheet2 is actually in an Excel Table (all data is in the XLS file, no external data sources). For clarity, I wanted to use a structured table reference for the combo box's Input Range, but cannot seem to find a syntax that works, e.g. myTable[[#Data],[myColumn3]]

I cannot find any indications that the combo box WILL accept structured table references, though I cannot see why it wouldn't.

So, two part question: 1. Is is possible to use a table column reference in the combo box input range property (not using VBA) and 2. HOW?


I realise this is quite an old question, but just in case anyone stumbles upon it like I just did looking to solve the same question... Like ScottieB, I too couldn't make dav's answer work for me. Here's how I solved it in Excel 2013.

  1. Create your vertical list of choices for your combo box. Not in a table (yet) not with a heading (yet, but leave space for one) just a column of whatever you need for your dropdown.

  2. Create/Define a name for your list using regular Sheet1!$A$1:$A$2 notation.

  3. Insert your Combo Box form control using the name you've just created for its Input Range.

So far so good. The Combo Box will work, but new options added to the bottom of the vertical list (even immediately below the named range) will not be added to the dropdown list; the named range, on which the dropdown is based, is not dynamically expanded.

Now for the good bit.

  1. Go back to your vertical list (make sure you clear anything you added around the named range so it's just as you left at step 2) and add a heading above it. Any text will do, it's just a label to identify your list of choices.

(Or don't! Excel will add one for you at the next step anyway, if you let it.)

  1. With the heading selected, use Ctrl+T or Insert | Table. Accept the range Excel finds and be sure to check the 'My tables has headers' box then click OK. You can change the name of the table if you like: it doesn't matter.

(Note: If you didn't add a header, don't check the 'My table has headers' box to have Excel create one for you.)

Now - apparently because the original named range is now part of a table definition - adding a new row to the bottom of your vertical list not only expands the table definition automatically, but also that of the original named range, Thus the new option will appear in the combo box's list of choices. Magic!

  • This appears to work with MS Excel for Mac version 16.18 (Office 365) as of 10/25/2018. It's still not as elegant as using the TableName[ColumnName] syntax that Excel uses everywhere else, but at least it does allow a user to add and remove items to/from the list and have them automatically show up in the combobox. – Sturm Oct 25 '18 at 18:34

In Excel 2010 you can do this, but its a two step process.

  1. Create a named range using the table structured reference (e.g. myrange=mytable[myColumn3].
  2. Use the named range as the control's input range.

And, as you'd expect, the control field updates with changes to the list. I'm not sure why you can't use the reference directly in the control-but there's lots about Excel I still don't understand.

EDIT: Don't forget to take the @ symbol out of the table reference in Name Manager, otherwise you'll only get the corresponding row's value for the validation set (e.g. [myColumn3] not [@myColumn3]).

  • 1
    I must be missing something. In Name Manager I'm creating a named range and setting equal to mytable[mycolumn]. Then right-click combo box to get to Format Control. In "Input Range" putting myRange (all permutations of quotes, equal sign). But still it won't fill. Any suggestions? – ScottieB Jul 8 '13 at 3:27

Nick's answer regarding XL 2013 is fine if you haven't created the table yet.

However it's simpler than that. You define a name pointing to the table column. Then create another name pointing to the first name. You can use the second name in the Input Range and it works fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.