Occupied with a text value.
the whole column is formatted as text

Ok, try this:


=IF(D2<"",ROW(),MATCH(REPT("z",255),D$1:D1))


--
Biff
Microsoft Excel MVP


"Greg Lovern" wrote in message
...
On Nov 16, 2:19 pm, "T. Valko" wrote:
I'm using this array formula to find the next occupied row up

Occupied with what exactly? A text value? A numeric value? A formula? A
formula that might return a blank ("") ? How about the entire column, is
the
data one type (text or numeric) or is it mixed?

Occupied with a text value. Not a formula.

It may be that someone sometime in the future may have to enter a text
value that looks like a number, but the whole column is formatted as
text, so Excel should treat it as text.


Thanks,

Greg


I don't think you need to use INDIRECT. Getting rid of INDIRECT will make
a
significant improvement (at least, it should). Also, this can be done
without array entering so there's some additional efficiency gained.

I'm assuming there will never be a completely empty column. If there is
your
current formula returns a #REF! error and the one I'm about to suggest
will
return an #N/A error.

*Assuming* the data is numeric *and* the values will *always* be <1000:

=IF(D2<"",ROW(),ROW(INDEX(D:D,MATCH(1000,D$1:D1)) ))

We can come up with a version that works for text or mixed values as
well.

--
Biff
Microsoft Excel MVP