UNIQUE in Named Range for Data Validation

[Veritan]

I am trying to make a Data Validation list from one of my tables, but I can't seem to get the UNIQUE function to work in the Name Manager. If I create a new Name called "Sports" and use the formula

=tblGear[Sport]

, it works just fine. However, I want the unique values from there. I don't want it to repeat "Baseball" 50 times before I get to "Basketball" in my drop-down list. But whenever I use the formula

=UNIQUE(tblGear[Sport])

or

=UNIQUE(INDIRECT("tblGear[Sport]"))

, it returns an error. Both of those work fine when I enter them into a blank cell on a sheet, but the Name Manager keeps saying that they return an error when I try to make a Name out of them. Does anybody have any idea why this is occurring, or how to fix it?

 

[Fluff]

You need to put the formula into a cell & then refer to that cell in the data validation.

 

[Veritan]

Thanks Fluff for the response. I tried that approach, and at least the Name Manager didn't error out. I used the formula

=Administration!$J$3

in the Name Manager to refer to the cell where I put the formula

=UNIQUE(tblGear[Sport])

. Unfortunately, I only got 1 result, even though the UNIQUE formula spilled into the cells below it on the actual worksheet. When I changed the Name Manager formula to be

=Administration!$J$3:$J$11

, I got the remaining items. But the entire point of the exercise is to create a dynamically adjusting list that will accommodate any additional values that may be added later.

I'm thinking that my only option may be to create another table and have it contain the unique values and then reference that field. I was hoping to avoid this just to help cut down on the number of objects on the sheet, but if that's my only choice, I guess I'll go with it. Let me know if you have any other ideas. Thanks for looking at this, though.

 

[Fluff]

You do not need to use the name manager, just put

=Administration!$J$3#

into the source box in data validation

 

[Veritan]

Thanks, that worked! And this way allows the list to grow dynamically on the Admin tab whenever I add anything to the main table. Thanks Fluff, appreciate the help!

 

[Fluff]

Glad to help & thanks for the feedback.