Formula to look upwards in column and return value below first blank cell encountered

[GTS]

Greetings,

This has turned out to be more challenging than I thought it would be. Here's my situation.

I have a worksheet with data in multiple blocks of rows (typ. 5 to 25 rows each). Each of these blocks is separated by a blank row. The blocks themselves have no blanks / blank rows.
The first row in each 'block' is a header row. Column J has a percent value entered into it.
What I want is for all the rows below it (within the block) to equal the value entered in the header row (in column J)
The formula has to accommodate a variable number of rows and processing whereby rows may be added or deleted within the block.
The block itself can shift up or down in the sheet as part of normal processing.

For example:
Say one of my blocks currently occupies rows 50 to 75. Cell J50 will have 80% entered into it. Cell J49 is blank. Cell J76 is blank.
I'm looking for a formula to use in J51 thru J75 that will pull in the value from J50 (80%).

Currently, I am using the following formula =INDIRECT(ADDRESS(ROW()-1,COLUMN()))
This does work nicely, but I'm a bit hesitant since this is a volatile function.
So far, it does not appear to be affecting the performance of my workbook, but I'd still like to know if there is a way without resorting to use of a volatile function.
Most solutions I've come across that are close to what I'm trying to accomplish require specification of a range which I think could be problematic.
Note - My thread title is just one way to express what I'm trying to achieve. Obviously the correct end result is what I'm after, however achieved.

Looking forward to your ideas.

 

[Jeffrey Mahoney]

OK, so here is a formula that could work. It currently needs a space in the blank between the blocks in column J. I think it would work better if you had a header that was the same at the top of each block instead. So the XMatch part would use whatever column your constant header string resides

=INDEX($J$1:J13,XMATCH(" ",$J$1:J13,0,-1)+1)

 

[Jeffrey Mahoney]

I'm hoping you have Office 365

 

[GTS]

Yes, I am using Office 365. Sorry, should have stated that.
I tried the formula and I'm getting an #N/A error. I did put a space in the blank cells.
I've never really used INDEX and MATCH much and admit to not knowing these functions very well.
I simply don't follow the $J$1:J13 part. The range is J1 to J13?

Follow up - Ok, got it to work. Realized that the J13 has to be the cell reference directly above the cell I'm in.
Where this potentially breaks down for me is when rows are inserted.
Below the insertion, the formula is now showing a range that that is several rows above the row I'm on.

 

[Fluff]

Yes, I am using Office 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Jeffrey Mahoney]

So, the range is anchored to row one in column J, but the second part of the range is not anchored. Meaning, when you copy the formula down, its only looking at the cells above the current row, not any cells below.

Can you post an example of the first row of one of your blocks or more.

 

[Jeffrey Mahoney]

We need adjust the formula for the first row, then we can copy it down. What is the first row you need this formula?

 

[GTS]

Jeffrey,

I was updating my reply (above) as you were posting. I realized the adjustment I needed to make to the range.
Unfortunately, the formula doesn't handle insertion of rows. A 'gap' is created. The range in the formula no longer extends down to the cell right above the active cell.
I do appreciate the effort, thank you.

 

[GTS]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Good suggestion. I have updated my account info. Thanks.