Dynamic Range with Dynamic Array to Fill Down? Unit Price Analysis Civil Eng Problem

[dejhantulip]

Dear community,

I turn to you with a problem that I am facing and that I have tried to come up with a solution but I cannot solve it yet.

In this worksheet I have formatted information laid out in a particular way or format that I have to maintain (D2:H39).
You can see that the "UNIT PRICE ANALYSIS" is kind of a form that may change in size (meaning more rows can be added) and if needed it can be pasted below so I might have 3 or 4 or more UNIT PRICE ANALYSIS forms. Again, not the best scenario for data, however I am restricted to working this way.

So basically I have been thinking and thinking of a way to use SEQUENCE to dynamically or automatically expand a helper column (column A) that would "fill down" the code found in Column C only when the "Activity:" word is on Column D. Also in Column C you can find other codes, but I only want the helper column to have the code whenever in Column D there is the word "Activity:".

You can see in Column B a manual help column (that must be dragged and dropped) that shows the desired result:

You can see in cells with yellow background and green text (B3 and B23) that when "Activity:" comes up it changes the code respectively. This is a good solution but it isn't dynamic.



So I tried to do the following idea with the SEQUENCE dynamic array function on Column A to try to dynamically replicate what I did on Column B:

Which I think has the "potential" to work because at first I am using the:

MAX(IF(ISBLANK(D:D),0,ROW(D:D)))

to find the row number of the last non blank cell in column D.
And then I am using the SEQUENCE function to generate a "dummy" (?) array to expand as the last non black cell changes as more forms are added or taken out.

And then finally I am using an IF funcion to try to repeat the formula or fill down. However, I believe it is "exactly" repeating the result of the formula and not evaluating it as it fills down.

To be honest, this is what I came up with, and I wanted to try to explain to the best of my abilities (sorry, English is not my first language) in order for someone to understand what I am trying to accomplish.

Any and all help is greatly appreciated

Also, if someone wants to give me another approach or formula (maybe a Lambda function), or something else that is not VBA I would be very very grateful


ORLANDO

 

[Eric W]

Punit's version is a nice improvement over mine, try that one. There are a few subtle differences, so test it out to make sure it works the way you want.