1

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 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.

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

1 Answer 1

1

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

0

2
  • @user3711262 This is such a simple, elegant solution; "Of cells from A-row-1 to A-this-row, note the row number of the non-blank cells and output the highest one." I'm curious if it worked in your real Sheet. Let us know.
    – Joel Reid
    Apr 18, 2019 at 16:57
  • Voted up, but just wonder if there is a simpler solution. Need another INDIRECT if you want the cell contents instead of the row number.
    – aamarks
    Jul 5, 2020 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.