The formula to return a cell reference is quite long. This makes it an ideal candidate for a custom function.
The formula to extract a cell reference is.
=SUBSTITUTE(CELL("address",A1),"$","")
The LAMBDA version is.
=LAMBDA(ref,SUBSTITUTE(CELL("address",ref),"$",""))(A1)
This is only how you test the LAMBDA function. This uses the ref parameter to capture the cell reference which is then passed to the CELL function within the SUBSTITUTE function to perform the calculation.
I have created a Custom Function called fnCELLREF to simplify this. See below.
I have started prefixing my custom functions with fn to list them together in the Name Manager and to differentiate them from standard Excel functions.
The range name dialog for this custom function is shown below.
The formula in the above dialog is.
=LAMBDA(ref,SUBSTITUTE(CELL("address",ref),"$",""))You use the Comment section of the Range Name to create an explanation/description of the function. This appears as you are typing the name – see image below.
I think I am missing something here.? Your LAMBDA function takes a cell reference as its argument in order to print that address in the cell as text, correct? Since you know the cell reference (you are typing it into your function), why can’t you just type that cell reference into the cell without using an equal sign in front so that it goes into the cell as text directly?
Hi Rick – If I type a cell ref in a cell I can’t copy it. Also by linking to a cell it updates if rows/columns are inserted.
Ah, I see where you are going now. I guess you could also define your LAMBDA function this way too…
=LAMBDA(ref,ADDRESS(ROW(ref),COLUMN(ref),4))
I doubt there is an advantage in doing it this way, but I thought it an interesting alternative.