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)