# Find previous non-blank cell, but without a specified range in Google Sheets

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:

.

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 g scripts if possible so it can remain dynamic.

Any thoughts?

edit1:

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

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.

• naw because header 1 is just the example, header 1 will always be different too – user3711262 Apr 15 '19 at 22:34
• so purely location based, not based on finding any matching characters – user3711262 Apr 15 '19 at 22:34
• not sure what does it have to do with B column. can you elaborate? – user0 Apr 15 '19 at 22:40
• ok edited my question to hopefully be more clear – user3711262 Apr 15 '19 at 22:58
• answer updated... – user0 Apr 15 '19 at 23:25

``=ARRAYFORMULA(MAX(IF(INDIRECT("A1:A"&ROW()-1)<>"", ROW(A:A), )))``