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

dejhantulip

Board Regular
Joined
Sep 9, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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:

PjOFZE4.png


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:

pj6ZBc4.png


Which I think has the "potential" to work because at first I am using the:
VBA Code:
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,545
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Forum statistics

Threads
1,144,342
Messages
5,723,813
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top