Unique Items in Drp-down List

[Chris Hankin]

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.

 

[Frank Kabel]

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)

 

[Chris Hankin]

Hello Frank,

Thanks very much for your help.

I am a little confused with the Helper column that you want me to
create.

Do I need to insert another column next to column E?

I am not exactly sure where I am supposed to enter in the following
formula:

=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)))

Please advise where exactly this formula is meant to go.

Kind regards,

Chris.

 

[Frank Kabel]

Hi
if your current list in in column D you may use column E as a helper
column (or any other column you want). Now insert the first formula in
E3 and the second one in cell E4. After this copy the formula from E4
to all other cells in column E for as many rows you need

 

[Bob Phillips]

Don't forget to point the SCA name at the new column RE as Frank said in his
original reply.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Chris Hankin]

Hello Frank,

Thanks again for all your help - much appreciated.

I entered in all your formalas and receieved the following Excel
warnings:

I inserted a Helper column in column E and entered the following formula
in E3 and copied down to the end of my list:

=IF(ISNA(MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0)),"",INDEX($D$3:$D$100
0,MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0)))

Excel warned that it could not calculate the formula. Cell references
in the formula refer to the formulas results, creating a circular
reference.

I changed the Name Definition of SCA to:

SCA =Offset('SA Register'!$E$3,0,0,COUNTA('SA
Register'!$E$3:$E$65536)-COUNTBLANK('SA Register'!$E$3:$E$65536),1)

Excel warns that the source currently evaluates to an error.

Could you please help as I do not know how to fix this.

Kind regards,

Chris.

 

[Frank Kabel]

Hi Chris
as mentioned in my previopus post, put the following in the cells in
column E:
Cell E3:
=D3

and in Cell E4 (not in cell E3) the formulas from below!

 

[Chris Hankin]

Hello Frank,

I am sorry about this going back & forth over this Excel problem, but I
am a newbie - so please do not be upset - I am trying to learn as best I
can.

In cell E3 I entered =D3.

I selected cell E4 and then entered in your formula:

=IF(ISNA(MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0)),"",INDEX($D$3:$D$100
0,MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0)))

in the formula bar. I then pressed Ctrl+Shift+Enter as it is an array
formula.

Excel warns me that there is an error in the formula.

I read the Excel help section on entering array formulas and got
nowhere.

Am I doing the right thing?

Kind regards,

Chris.

 

[Frank Kabel]

Hi Chris
no problem
this formula works for me. One idea: You also use an english Excel
version and you also use the coma (and not the semicolon) as
separator?.
If this is not the problem you may email me your file and I'll insert
the formula for you:
email: frank[dot]kabel[at]freenet[dot]de