Removing Multiple Instances from Data Validation Lookup

You have to create a distinct list from the range that INDEX formula is applied to.

Ummm How do you do that? Can you use IF or COUNTIF functions within validation? Or is it something else?

ammdumas said:

Ummm How do you do that? Can you use IF or COUNTIF functions within validation? Or is it something else?

Click to expand...

If SUMMARY refers to a range that is static (a range where calculations take place) and you want to use the distinct set of items from its 2nd column (as the INDEX formula you posted indicates) in many data validated cells, I'd suggest invoking a formula system that creates a list of distinct items on the same (or on some other) sheet. Do you want to see an example?

yes please. ray:

ammdumas said:

yes please. ray:

Click to expand...

Formulas...

C1 must house a 0.

C3, copied down:

=IF((B3<>"")*ISNA(MATCH(B3,$B$2:B2,0)),LOOKUP(9.99999999999999E+307,$C$1:C2)+1,"")

D1:

=LOOKUP(9.99999999999999E+307,C:C)

D3, copied down:

=IF(ROW()-ROW(D$3)+1<=$D$1,LOOKUP(ROW()-ROW(D$3)+1,C:C,B:B),"")

Now you need to create a name that refers to the range in D.

Activate Insert|Name|Define.
Enter DList (or some such) as name in the Names in Workbook box.
Enter the following in the refers to box:

=Data!$D$3:INDEX(Data!$D$3:$D$56536,MATCH("*",Data!$D$3:$D$56536,-1))

Click OK.

You can invoke

=DList

as Source in a cell that you want a dropdown list using data validation.

All this can be set up to be fully automatic on Excel 2003. Are you on Excel 2003?

Ha! That would mean my company actually being 'with' the times. Nope. Still using Excel2000.

I'll have to try it manually. In any case, I'll give this a whack and see if it works. If not...not a HUGE deal. It would have been a really-nice-to-have more than anything. At least the similar values will never be scattered. They'll always be together (in bunches of 2 or more) and in a sequential order (hey, I wonder if that makes your formula easier?). Thanks for all the help. Cheers.

P.S. I'll be in your neck of the woods in April (Arnhem)

ammdumas said:

Ha! That would mean my company actually being 'with' the times. Nope. Still using Excel2000.

I'll have to try it manually. In any case, I'll give this a whack and see if it works. If not...not a HUGE deal. It would have been a really-nice-to-have more than anything. At least the similar values will never be scattered. They'll always be together (in bunches of 2 or more) and in a sequential order (hey, I wonder if that makes your formula easier?). Thanks for all the help. Cheers.

Click to expand...

Don't worry. The system will work as advertised and grouped values won't make any difference.

P.S. I'll be in your neck of the woods in April (Arnhem)

Click to expand...

That's close enough to where I live.

Excellent. Later.