Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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]).

share|improve this answer
    
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

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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