Convert vba to formula with Named Ranges

[detriez]

I need to convert this to a formula
If a cell is not blank, concatenate the Header (nMerge1, nMerge2, nMerge3 etc) and the cell value
I use a named range for the header because the header will always change.

The challenge is, columns may be inserted or deleted which can change the column reference.. Would a named range be applicable here too?
Not every cell in the column will be populated so I cant use End(xlUP).Row

lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1

    Application.ScreenUpdating = False
    With Range("AL2:AL" & lr)
        .FormulaR1C1 = "=IF(RC[1]<>"""",CONCATENATE((R1C39&"": ""),RC[1])&CHAR(10),"""")&" & _
                       "IF(RC[2]<>"""",CONCATENATE((R1C40&"": ""),RC[2])&CHAR(10),"""")&" & _
                       "IF(RC[3]<>"""",CONCATENATE((R1C41&"": ""),RC[3])&CHAR(10),"""")&" & _
                       "IF(RC[4]<>"""",CONCATENATE((R1C42&"": ""),RC[4])&CHAR(10),"""")

My formula.. Should this be AND or OR?

=IF(AN2<>"",nMerge1&": "&AN2,"")&CHAR(10)
IF(AN2<>"",nMerge2&": "&Ap2,"")&CHAR(10)
IF(AN2<>"",nMerge3&": "&AP2,"")&CHAR(10)
IF(AN2<>"",nMerge4&": "&AQ2,"")&CHAR(10)

This should be the end result

 

[detriez]

Hi Fluff.. This works great.
I worried that adding or removing a column before Column M would interfere with the cell and column references but Excel seems to be compensating.

This will end up being an Excel 365 shared workbook so (unless I'm wrong) VBA is not an option.
What would be a good way to copy the formula down to the last row?

 

[Fluff]

What would be a good way to copy the formula down to the last row?

Double click the bottom right hand corner of the cell. It will then fill down to the next blank cell in a neighbouring col.

 

[detriez]

Can you think of a way to automate that so the formula populates down to the last used row?
I'd hate to copy the cell down to 1,048,576

 

[Fluff]

As long as you don't have blank cells in a neighbouring col (say AL) then it will just copy down to the last used cell.

 

[detriez]

Got it.. Thanks Fluff. Works as needed

 

[Fluff]

You're welcome & thanks for the feedback.