Google Sheets find next/previous non-blank cell, but without a specified range

by user3711262   Last Updated April 15, 2019 23:03 PM

I've come across plenty of formulas to find blank cells within a certain specified range, but can't seem to figure this out / find an answer.

I have a spreadsheet kind of like this:

enter image description here.

Where column A is empty except for these header bars, and there are lots of sections going down the sheet with these header bars.

What I'm trying to do is have a formula that can find the row of the cell A header from within a section, but so that the formula will work in any section.

So in the above "sheet", let's say I was in cell B52. Maybe there's a formula to look at A52, and if empty keep moving up until it found that cell A48 was the first cell upwards to have a value, and return the value as 48 (the row).

I'm also hoping this can be done with regular formulas and avoid using gss if possible so it can remain dynamic.

Any thoughts? Thanks!!

edit1:

apparently not quite clear enough. I made another sheet to help illustrate further what the data looks like and what I'm after:

enter image description here

I'm hoping for a formula that when placed in cell B32 as highlighted, it will return A28, or just 28, as in the row of the previous non-blank cell, but then ideally, the same formula could then be placed anywhere else, say for example B46, which would then return A37 as the previous non-blank cell in the A column.

Tags : google-sheets


Answers 1


=MAX(ARRAYFORMULA(IF(A:A<>"", ROW(A:A), )))

0

user0
user0
April 15, 2019 22:46 PM

Related Questions


Updated March 03, 2018 02:03 AM

Updated September 19, 2018 13:03 PM

Updated June 10, 2019 17:03 PM

Updated July 26, 2018 21:03 PM

Updated November 29, 2018 16:03 PM