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