Dynamic Unique List 2

[Scott24x7]

Hi All,
Long time without a question, but I'm working on something new...

So I have used named ranges to provide for source data in dropdown lists. And I like to do them dynamically, so as I add to list data, it appears without having to redefine fixed ranges.

But now I have something a little different. I have a column of data, that has repeating values, that I want to use as my source, but the length of the column is also variable.
Previously I would have created a named range like:

MyRange with the formula:
=OFFSET('Static Variables'!$F$4,0,0, COUNTA('Static Variables'!$F:$F),1)

This works find, but gives me one entry for every row.

I just want the unqiue values out of that same range, and I tried both:

=OFFSET('Static Variables'!$F$4,0,0, COUNTA(UNIQUE('Static Variables'!$F:$F)),1)
and
=UNIQUE(OFFSET('Static Variable'!$F$4,0,0, COUNTA('Static Variables'!$F$F),1)

And neither works.

Is there a way to use the UNIQUE or other function to provide for the unique values while keeping the range dynamic?


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Andrzejek]

You may either:
Copy your list, paste it into another (hidden?) sheet and remove duplicates, or
Dive into Excel's Power Query or
Use UNIQUE function

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Scotty,

Isn't UNIQUE() the result you're looking for WITHOUT using OFFSET()?

BTW, if you're looking to define dynamic ranges, why are you still using A1 notation? Why not use a Structured Table?

So I'm using Insert > Tables > Table and naming my Structured Table, tList.

[pre]
My List
A
B
A
C
[/pre]

In the Name Manager, where you want to assign a dynamic named range...
[tt]
=UNIQUE(tList)
[/tt]

BTW, if you use A1 notation and reference the entire column, UNIQUE will return a ZERO for the unique value of all empty cells, so in my example, you'd see 0, A, B, C.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Scott24x7]

Hi Skip,
Didn't you retire?

I tried this, but it's not working. Any time I put in the data validation for the source for a List, I get an error, saying that "The Source currently evaluates to an error. Do you want to continue?" with Yes/No buttons. If I say yes, then when I click on the dropdown in the cell, there is nothing (not blank, nothing). Whatever content (if any) was in the cell before is still there, and there are no options to pick.

Did I do something wrong?
I have a named range called StandardsRange with it's definition as -UNIQUE(tStandard) where tStanndard is a table I created using the method you showed above, and I put about 6 rows with 2 repeating values in it.

Not sure what's happening.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[SkipVought]

You cannot use a Structured Table name in Data > Validation. You must use a Range Name.

Are you using StandardsRange in your Data > Validation?

Yes, I've retired, but I still dabble in Excel & VBA

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Maybe this will help....

Right or wrong, what I did was: Start Excel, Insert - Table

OK
That gave me this:

so, I created my list of names:

So far, so good.
Then I selected Table1 and typed over it tList

Like this

Then in cell C1 I typed: =UNIQUE(tList)

And if you want to have it sorted:

Skip, is that correct

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Andy, yes, although the =UNIQUE(tList) is not on the sheet, but in the Names Manager.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

You cannot use a Structured Table name in Data > Validation.

Not directly, but table name + column name can be converted to range for DV: [tt]=INDIRECT("tSourceTable[Field_1]")[/tt]


combo

 

[Scott24x7]

Thanks Skip and Andy. I got this working!
Cheers,
-S


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[SkipVought]

Plz let us know the solution that worked.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Scott24x7]

It was a combination, but also I had some misunderstanding it its implementation. It doesn't "Auto update" like a dynamic range might, you have to click the "Refresh" on the table, with the dynamic range on the "UNIQUE" in the set next to the table for it to work. (Usually I would just "add" an item to a dynamic range, but tables don't update the same way, you have to "refresh" them to build them.

It's a little problematic for my users skillset, but I think I can work through them with it.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.