Hi Chris
you have to create a helper column which only contains the unique
entries. And then refere to this helper column. e.g. enter the
following formulas on your 'SA Register' sheet:
E3:
=D3

E4: Enter the array formula (entered with CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0) ),"",INDEX($D$3:$D$10
00,MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0)))
and copy this down as far as needed

After this use nthe following formula for your name definition:
SCA =Offset('SA Register'!$E$3,0,0,COUNTA('SA
Register'!$E$3:$E$65536)-COUNTBLANK('SA Register'!$E$3:$E$65536),1)


--
Regards
Frank Kabel
Frankfurt, Germany

"Chris Hankin" schrieb im Newsbeitrag
...
Hello,

Could someone please help me modify the code below so that it does
the
following:

In my Excel workbook I have a worksheet named: Lookup

In cell B13 there is a drop-down list. The drop-down list was
created by
using Data Validation.

The Data Validation criteria is: Allow = List & Source =SCA

The code I am referring to is this code: SCA =Offset('SA
Register'!$D$3,0,0,COUNTS,('SA Register'!$D$3:$D$65536),1)

This formula selects column D on the worksheet named SA Register. It
selects from cell D3 to the last cell in column D with data in it.
There
are no blank cells in column D.

Unfortunately, it selects all data in column D. I need it to select
only
unique items listed in column D.

Any help is greatly appreciated,

Kind regards,

Chris.