UNIQUE in Named Range for Data Validation

Veritan

Active Member
Joined
Jun 21, 2016
Messages
383
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
Excel 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
Excel Formula:
=UNIQUE(tblGear[Sport])
or
Excel Formula:
=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?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,921
Office Version
  1. 365
Platform
  1. Windows
You need to put the formula into a cell & then refer to that cell in the data validation.
 

Veritan

Active Member
Joined
Jun 21, 2016
Messages
383
Thanks Fluff for the response. I tried that approach, and at least the Name Manager didn't error out. I used the formula
Excel Formula:
=Administration!$J$3
in the Name Manager to refer to the cell where I put the formula
Excel 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
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,921
Office Version
  1. 365
Platform
  1. Windows
You do not need to use the name manager, just put
Excel Formula:
=Administration!$J$3#
into the source box in data validation
 
Solution

Veritan

Active Member
Joined
Jun 21, 2016
Messages
383
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,921
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Forum statistics

Threads
1,176,685
Messages
5,904,437
Members
435,092
Latest member
armywalrus2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top