Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

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.