Using formulas entered as a named range as input range for a ComboBox results in 'error

[RobertG1951]

As I understand it, you can't directly use formulas for the input range in ComboBoxes.  You have to use Name Manager, assign an name and formula, and then you can enter the assigned name for an Input Range. However, when I attempt this in the beta version of Excel for Office 365, I get the error, "Reference isn't valid" I tested this with the formula, "=UNIQUE(Table1[Division])" as the most straightforward example but have yet to find a formula that doesn't generate an error.

Please explain why this doesn't work and what formulas will work.

[#2]

It is my understanding that data validation requires range references and not arrays.

=UNIQUE(Table1[Division]) would not return a reference to a range, but an array. If you entered that formula in a cell, say A1, then you could use a named formula such as:

UniqueList =offset(a1,0,0,counta(UNIQUE(Table1[Division])),1)

which would return a range reference and, I believe, could be used for data validation.