"Pat" wrote:
Max,
I am quite interested in your suggestion for using in another part of the
workbook. I have tried it out and it almost worked for me.
I say almost because when I deleted a cell in colC the result of the
formula
in colE shifted up one cell instead of staying where it should be. What
this
has meant is the values of colC and colE are nolonger on the same row.
Maybe this is meant to work this way because the dropdown list
is working as you intended it to do.

It'll all stay in sync if you delete the entire row instead of shifting up
one cell in col C <g.

One alternative to always point to the same range of cells in col C
(C77:C2000) irrespective might be to use INDIRECT, for example
we could try instead:

In D77: =IF(INDIRECT("C"&ROW(A77))="","",ROW())

In E77:

=INDEX(INDIRECT("$C$77:$C$2000"),MATCH(SMALL($D$77 :$D$2000,ROWS($A$1:A1)),$D
$77:$D$2000,0))

with D77:E77 copied down to E2000 as before

The above means that we always want to point only to those cells in the
range: C77:C2000 as the target source, so whatever gets shifted up before
C77 (e.g.: to C76, C75, etc from where they were formerly within C77:C2000)
will be "lost" from the DV.

One other thing I noticed is the error #NUM! on in some
cells at the bottom of colE where there is nothing to calculate in some
cells in colC.

The error cells are used / counted by the SUMPRODUCT in the defined range
formula to compute the number of items to show in the droplist (in a
converse manner, so as to speak)

Should the appearance of the error cells bother you, you could conditionally
format the range E77:E2000 with the formula: =ISERROR(E77), and choose a
font color to blend-in with the fill color (white?)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----