Data Validation list with unique values (drop down menu)

[0123ren]

I have a column of numbers which contains numerous repeated entries and blank cells. The list is free to expand as people can continually add to the list. The list can only expand to a finite number of rows, say 150.

Is it possible to use a bit of VB code to extract only unique entries and non-blanks from this list and copy it to another area so that this "new_list" can be named and used as data validation for a drop down menu.

I would like it to be automated so the length of the new_list created is recognised by the data validation so that there are not numerous blanks at the bottom of the drop-down menu as may be the case if numerous duplicate entries and blanks are removed.

I would be extremely greatful for any assistance.

PS I have looked at many related threads and although many solutions closely approach the one I require there are no direct matches. I am not a seasoned professional when it comes to excel so if I have failed to explain my situation using the correct terminology then please feel free to ask for further explanation.

Thanks in advance for any assistance offered!!

 

[0123ren]

I suppose a little example of what I require would be helpful.

A1
280
260
125
260

125
280
320

260
125
320


Drop down menu in a cell on another sheet would display.

125
260
280
320

in other words only the 4 unique numbers without any blanks at the bottom.

Again, thanks in advance, I hope this example is clear enough.

 

[Krishnakumar]

Hi,

This might be useful.

 

[0123ren]

Thanks Kris, that looks precisely what I need.

 

[Krishnakumar]

Hi,

You are welcome !!

 

[BeachRock]

What might be useful? Found this post looking for the same answer but there isn't any answer to look at... I know this is an old post but, shouldn't the answer provided to the original poster still be available for others to use too?

 

[Aladin Akyurek]

What might be useful? Found this post looking for the same answer but there isn't any answer to look at... I know this is an old post but, shouldn't the answer provided to the original poster still be available for others to use too?

Either register with the site in order to get the file or post a small sample here along with the results that you want to obtain.

 

[BeachRock]

I am registered on the mrexcel forum....

Do you see anything at all in Krishna's first post other than a reference to something that might help? There is no URL or file attachment...

 

[Aladin Akyurek]

I am registered on the mrexcel forum....

Do you see anything at all in Krishna's first post other than a reference to something that might help? There is no URL or file attachment...

You have also to register with the site of the below link:

This

 

[BeachRock]

Thank you, Aladin. I would have never known since the link wasn't there for me.