*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 even reduce that further to:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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?

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


"Greg Lovern" wrote in message
...
I'm using this array formula to find the next occupied row up, in
another column, from the current row:

{=(IF(D2<"",ROW(),ROW(INDIRECT("D"&MAX(IF($D$1:D2 ="",0,ROW($D
$1:D2)))))))}

It works, but the data is already over 13,000 rows, and the project is
trending toward around 30,000 rows. The formula is fast for the first
few thousand or so rows, but becomes very slow toward the last few
thousand or so rows, due to doing so many comparisons.

Is there a more efficient way to get the next occupied row up, in
another column, from the current row?

There probably won't ever be more than a thousand rows between
occupied cells (though there could be; there is no limit), so I could
change the formula at around row 1,000 to only look as far up as 1,000
rows above. That would calculate faster, but eventually this will be
used by others who will be inserting and deleting rows, and I'd prefer
something more robust.

Any ideas?


Thanks,

Greg