Skip multiple blanks
 

The following details will skip blank cells in a range so that when creating
a custom validation list there will be no blanks.

BA4:BA500 contain customer names

BB4:BB500 contains the formula:
=IF(INDIRECT("BA"&ROW(IV4))="","",ROW())

BC4:BC500 contains the formula:
=INDEX(INDIRECT("$BA$4:$BA$500"),MATCH(SMALL($BB$4 :$BB$500,ROWS($IV$1:IV1)),
$BB$4:$BB$500,0))

Defined range with the name SkipCustomerBlanks:
=OFFSET(Customer!$BC$4,0,0,SUMPRODUCT(--NOT(ISERROR(Customer!$BC$4:$BC$500))
),)

When a customer name is deleted in BA the range BC4:BC500 then moves up one
cell and the error #NUM! appears on the bottom cell. This does not cause a
problem, the problem I am encountering is when a large number of customer
names are deleted there comes a point where instead of the error #NUM!
appears a zero (0) is returned instead. Where a zero is returned to a cell
in BC4:BC500 the validation list will then display the zero (0) in the
dropdown list.

Anyone know what causes this to happen?
Much appreciate if you can help.
Pat

I have found what was causing the zeros to appear in the val list. Some
cells contained a formula which returned zero. I have removed these
formulas.

Pat


"Pat" wrote in message
...
The following details will skip blank cells in a range so that when
creating
a custom validation list there will be no blanks.

BA4:BA500 contain customer names

BB4:BB500 contains the formula:
=IF(INDIRECT("BA"&ROW(IV4))="","",ROW())

BC4:BC500 contains the formula:

=INDEX(INDIRECT("$BA$4:$BA$500"),MATCH(SMALL($BB$4 :$BB$500,ROWS($IV$1:IV1)),
$BB$4:$BB$500,0))

Defined range with the name SkipCustomerBlanks:

=OFFSET(Customer!$BC$4,0,0,SUMPRODUCT(--NOT(ISERROR(Customer!$BC$4:$BC$500))
),)

When a customer name is deleted in BA the range BC4:BC500 then moves up
one
cell and the error #NUM! appears on the bottom cell. This does not cause a
problem, the problem I am encountering is when a large number of customer
names are deleted there comes a point where instead of the error #NUM!
appears a zero (0) is returned instead. Where a zero is returned to a cell
in BC4:BC500 the validation list will then display the zero (0) in the
dropdown list.

Anyone know what causes this to happen?
Much appreciate if you can help.
Pat