Data Validation using Structured References and UNIQUE function

[scecchin]

Hello

I am working with structured references (tables). I have a table, (example enclosed), that contains data. I have kept the example simple. The table is a data table with one column called Number. The table name is Table1. For the application I am trying to address, this example table is a data table and in the column Number, there would be redundant values. I want to create a data validation list of the unique values in the table so the user can only select what is there, but not see the redundancy. There are some screen shots of the settings in Data Validation for List in the attached file. If I put the following Source field, << =INDIRECT("Table2[Number]") >>, it will list the values in Table1[Number], but it show the redundant values as well, and the values are not sorted. When I try to sort and remove redundant values with the SORT and UNIQUE functions, and embed in the Data Validation Source field, Excel tells me it will end in error. This happens even if I try to wrap the formula using INDIRECT

Appreciate whatever assistance can be provided

Thank you for your time
Sergio

 

[Michael Gramm]

Set your validation to List using the formula =OFFSET(C42,,,COUNT(C42:C51))

 

[scecchin]

Thank you for your feedback, Michael, much appreciated. I was looking for a solution where I did not need to generate the sorted and unique list (C42), and then reference that. I am trying to build this directly from the structured reference table, something like the =INDIRECT("Table1[Number]"), which does work to produce the list, but not sorted or unique

 

[p45cal]

Would the DV in cell C18 of the attached be good enough?
It refers to a hidden sheet called Control which contains a simple SORT(UNIQUE… formula.

 

[Debaser]

You cannot do it directly in the DV formula.

 

[scecchin]

Would the DV in cell C18 of the attached be good enough?
It refers to a hidden sheet called Control which contains a simple SORT(UNIQUE… formula.

Thanks for the feedback, p45cal. Agreed, this can be managed that way. Was just trying to push the envelope and avoid that intermediary step, but I acknowledge this will work

 

[scecchin]

You cannot do it directly in the DV formula.

Thanks Debaser. Was hoping I was missing something since other formula actions do work there, but does not seem to be the case here. Thanks for the feedback

 

[Debaser]

It only works if the formula returns a range. UNIQUE and SORT return arrays.