Formula that returns reference for a value

[Hall]

I have a column of values. I need a formula that returns the row number of
the cell that has a blank in it.

I can't believe that doesn't exist...

Anyone?

 

[Peo Sjoblom]

One way

=MATCH(TRUE,A1:A10="",0)

entered with ctrl + shift & enter will return the first blank cell in a
range
if the range starts somewhere else like in A5 and you want the absolute row
number

=CELL("row",INDEX($A$5:$A$20,MATCH(TRUE,$A$5:$A$20="",0)))

entered with ctrl + shift & enter

 

[A.W.J. Ales]

Very neat Peo.
For the second part however I would - since the row of the first cell of the
range is known - prefer :

=MATCH(TRUE,$A$5:$A$20="",0) + Row($A$5) - 1 ; also entered with ctrl +shift
&enter

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Peo Sjoblom]

I agree, it's an improvement using the +ROW