Extracting unique entries and assigning it to a named range
 

Hi,

(Im cross-posting it to Misc group).

I want to create a name in the named box which would store unique list
of values from a range lets say A1: A100. Im thinking of using this
name in a validation drop-down box as listed in Contextures site. What
kind of formula can I write in the named box which would help me
achieve this. I want to avoid using using worksheet real estate to do
the calculations and still be able to keep my named range dynamic so
that if A1:A100 increases to a1:A1000 my validation box remains
current.

(I have also seen the Uniqueitems function code in
http://www.j-walk.com/ss/excel/tips/tip15.htm but prefer a code less
solution.)

Please guide me.

Regards,
Hari
India

Hari wrote:
Patrick and keepITcool,

Thanks for a detailed solution using dictionary method.

Im sorry, actually I need to send this across to somebody else and
prefer not instructing the other person to add references to in their
VB (the person wouldnt like to get in to VB environment). Hence, a
formula based solution (or if not possible then a code which would
return an array without setting of references) would be preferable.
(Can the formula based soltuion be made dynamic using a variant of
indirect formula, so that if list changes from A1:A1000 then also it
works.)

(actually am planing to use this unique list in a validation box.)

Regards,
Hari
India

PS: I should have posted it to Misc group rather than programming group.