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.