The easiest way to add additional columns would be to insert a new row, say row 3. In this row you would enter a value corresponding to the column - eg cell B3 value 1, cell C3 formula =B3+1, cell D3 formula =C3+1 and so on to the last column.
Then the formula, which would now be first inserted in cell K4, would be:
=IF(COUNTBLANK(B4:J4)<> 9,INDEX($B$2:$J$2,1,MAX(ISTEXT(B4:J4)*($B$3:$J$3)),"")
You then have to update the formula to change the column identifier from J to the last column letter in your new sheet and the value 9 to the value in the last cell in new row 3. (Note: this is a good example of why it is considered poor practice to use constants in formulae
).
A degree of automation can be achieved by, erring from the KISS principle
, using named ranges. Define the following names:
Dates $B$2:$J$2
ColCount $B$3:$J$3 (Using a new row as above)
LastCell $J$3
The formula would then be:
=IF(COUNTBLANK(B4:J4)<>LastCell,INDEX(Dates,1,MAX(ISTEXT(B4: J4)*(ColCount))),"")
Then after inserting columns the formula 'should' update automatically once rows 2 & 3 have data.
Hope this helps
EG