CODEWITHSUNDEEP
arraysexcelformuladynamic-arraysexcel-365
Nick
Nick

Reputation: 875

Dynamic Helper Column?

I wish to create a helper cell using a dynamic array, Is this possible?

Normally I use a formula such as in this example =IF(A4="",C2,A4) which check if column A is blank and if so just copies the above value until a new value appears in column A.

However as shown in the image when I insert New rows the helper cell becomes broken.

For this reason I wanted to use a dynamic array to create the helper cell which should be able to handle rows inserted or removed.

The problem I have is that unlike a normal formula which you can just drag down, a dynamic array is looking at the values as a whole. Using =IF(A4:A40="","-",A4:A40) inserts a hyphen as shown in the image. Essentially I wish to replace the hyphen such that "For each value between A4:A40 check if blank and if true take the value in the above cell". In essence creating a dynamic helper column.

enter image description here

Upvotes: 1

Views: 271

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34420

You can use the inexact form of Match to find the last row which is not blank, and then Index to take the value in column A from there:

=INDEX(A4:A40,MATCH(ROW(A4:A40),IF(A4:A40<>"",ROW(A4:A40))))

This sort of thing is done a lot in Google Sheets.

enter image description here

If you insert a row, the formula changes to

=INDEX(A4:A41,MATCH(ROW(A4:A41),IF(A4:A41<>"",ROW(A4:A41))))

and still works OK.

Upvotes: 1

Related Questions