Can't create blank from IF - any way around

[Bingo Accent]

Hi,

I'm using the following to create a data validation list:

=OFFSET(C$100,0,0,COUNTA(C$100:C$200),1)

At C100 through to C200 I have

=IF(ISTEXT('Status list'!C1),'Status list'!C1,"")

(Status List is a separate worksheet of options for various drop-down
lists through the workbook - you can't use data validation with
references across worksheets)

The idea is that I can, as time goes by, add options on the Status List
sheet - up to 100 options), without needing to play around with data
validation.

The problem I have, is that "" is not interpreted as blank by the data
validation, with the result that the drop-down list appears empty
unless you scroll up to find the actual list at the top.

Having had a look at other posts I see that IF formulae cannot return a
true blank (I've even tried using a reference to a blank cell, but this
still returns a '0').

Any suggestions???

[Bob Phillips]

Try

=OFFSET(C$100,0,0,SUMPRODUCT(--(C$100:C$200<>"")),1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bingo Accent" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I'm using the following to create a data validation list:
>
> =OFFSET(C$100,0,0,COUNTA(C$100:C$200),1)
>
> At C100 through to C200 I have
>
> =IF(ISTEXT('Status list'!C1),'Status list'!C1,"")
>
> (Status List is a separate worksheet of options for various drop-down
> lists through the workbook - you can't use data validation with
> references across worksheets)
>
> The idea is that I can, as time goes by, add options on the Status List
> sheet - up to 100 options), without needing to play around with data
> validation.
>
> The problem I have, is that "" is not interpreted as blank by the data
> validation, with the result that the drop-down list appears empty
> unless you scroll up to find the actual list at the top.
>
> Having had a look at other posts I see that IF formulae cannot return a
> true blank (I've even tried using a reference to a blank cell, but this
> still returns a '0').
>
> Any suggestions???
>