Datavalidation and unique with match and offset ERROR

[quarna]

HI

=offset(beregning!$C$1,1,match(beregning!$C$1,beregning!$C$1,0)-1,count(offset(beregning!$C$1,1,match(beregning!$C$1,beregning!$C$1,0)-1,10,1)),1)

This line works fine with datavalidation list, but i have multiple duplicates in the sheet and i dont wont them in the list.


=unique(offset(beregning!$C$1,1,match(beregning!$C$1,beregning!$C$1,0)-1,count(offset(beregning!$C$1,1,match(beregning!$C$1,beregning!$C$1,0)-1,10,1)),1))

this line works in a cell, but when trying to put it in datavalidation list, it returns an error and does not function at all.


::::
I have replaced ; with , and renamed to formulas to english version, so it might be a little of here, i dont know.

Thank you
/kim

 

[Fluff]

You cannot use a formula like that in data validation, however you could use this formula in another cell & refer to that in the DV.

=UNIQUE(beregning!C2:C11)

If that formula is in A2 you would simply use

=A2#

in the DV

 

[quarna]

Hi

Thank you.

Honestly i thought i already had tried the way you said, but i guess i havent, because that works almost fine for me.
There is 1 thing that dosnt work though.
When the range C2:C11 changes values. The validationlist dosnt show the newest value until you press the arrow to openup the list.
Why is that ?

 

[Fluff]

How do you know the DV doesn't show the changes without clicking on the arrow?

 

[quarna]

How do you know the DV doesn't show the changes without clicking on the arrow?

The visible cell shows the "old" value until you press the arrow and open the list.
But i understand thats how validation function works after browsing around the forum.
So i guess i have to find some other way around it

 

[Fluff]

But i understand thats how validation function works

That is indeed how it works. Changing the list does not affect what is already in the cell.