Search code examples
arraysexcelformuladynamic-arraysexcel-365

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

Solution

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