Is this the best way to extract the cell address from a FormulaText expression?

[JenniferMurphy]

Before the FormulaText function was available, I wrote my own little GetFormula UDF. (See below) It had an optional second parameter that would include the cell address before the formula text. I found that very helpful as I often needed to put the GetFormula calls somewhere other than right next to the cell in question.

The FormulaText function lacks this option. I'll probably rewrite my GetFormula UDF to make use of the new FormulaText function. In the meantime, I came up with this solution. Is there a better way? I am referring to the expressions in D2:D4. My formula text really threw xl2bb a curve.

Take Function.xlsx
	C	D	E	F	G	H	I	J	K
2	Ave1	F8	=LET(ridx,ROW()-ROW(Table14[#Headers]),AVERAGE(DROP(TAKE(Table14[Value],ridx),MAX(0,ridx-N))))
3	Ave2	H8	=LET(ridx,ROW()-ROW(Table14[#Headers]),AVERAGE(INDEX(Table14[Value],SEQUENCE(MIN(ridx,N),,ridx,-1))))
4	Ave3	J8	=MAP(SEQUENCE(ROWS(Table14)),LAMBDA(r,AVERAGE(DROP(TAKE(Table14[Value],r),MAX(0,r-N)))))
5
6	N=3
7	Value		Sum1 Bot N	Ave1 Bot N	Sum2 Bot N	Ave2 Bot N	Sum3 Bot N	Ave3 Bot N
8	3		3	3.00	3	3.00	3	3.00
9	6		9	4.50	9	4.50	9	4.50
10	1		10	3.33	10	3.33	10	3.33
11	7		14	4.67	14	4.67	14	4.67
12	3		11	3.67	11	3.67	11	3.67
13	1		11	3.67	11	3.67	11	3.67
14	2		6	2.00	6	2.00	6	2.00
15	5		8	2.67	8	2.67	8	2.67
16	3		10	3.33	10	3.33	10	3.33
17	8		16	5.33	16	5.33	16	5.33
MrExcel

Cell Formulas
Range	Formula
D2:D4	D2	=LET(txt,FORMULATEXT(E2),NLeft,FIND("(",txt,1)+1,MID(txt,NLeft,LEN(txt)-NLeft))
E2	E2	=FORMULATEXT(F8)
E3	E3	=FORMULATEXT(H8)
E4	E4	=FORMULATEXT(J8)
E8:E17	E8	=LET(ridx,ROW()-ROW(Table14[#Headers]),SUM(DROP(TAKE(Table14[Value],ridx),MAX(0,ridx-N))))
F8:F17	F8	=LET(ridx,ROW()-ROW(Table14[#Headers]),AVERAGE(DROP(TAKE(Table14[Value],ridx),MAX(0,ridx-N))))
G8:G17	G8	=LET(ridx,ROW()-ROW(Table14[#Headers]),SUM(INDEX(Table14[Value],SEQUENCE(MIN(ridx,N),,ridx,-1))))
H8:H17	H8	=LET(ridx,ROW()-ROW(Table14[#Headers]),AVERAGE(INDEX(Table14[Value],SEQUENCE(MIN(ridx,N),,ridx,-1))))
I8:I17	I8	=MAP(SEQUENCE(ROWS(Table14)),LAMBDA(r,SUM(DROP(TAKE(Table14[Value],r),MAX(0,r-N)))))
J8:J17	J8	=MAP(SEQUENCE(ROWS(Table14)),LAMBDA(r,AVERAGE(DROP(TAKE(Table14[Value],r),MAX(0,r-N)))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
N	=MrExcel!$C$6	I8:J8, E8:H17

Here's my GetFormula UDF:

Function GetFormula(pCell As Range, Optional pAddrSw As Boolean = True) As String

GetFormula = pCell(1).FormulaArray      'Get the formula in the cell
If pCell(1).HasArray Then               'If it's an array formula,
  GetFormula = "{" & GetFormula & "}"     'Add the {}s
End If
                                        
If pAddrSw Then                         'If switch is on
  GetFormula = pCell(1).Address(0, 0) & ": " & _
    pCell(1).PrefixCharacter & GetFormula 'Add the cell address and leading quote, if any
End If

End Function

 

[awoohaw]

You could use the ADDRESS() function:

Book1
	I	J
1
2	2	$I$2 =1+1
3	2	I$3 =1+1
4	2	$I4 =1+1
5	2	I5 =1+1
Sheet1

Cell Formulas
Range	Formula
I2:I5	I2	=1+1
J2,J4:J5	J2	=ADDRESS(ROW(I2),COLUMN(I2),ROW()-1)&" "&FORMULATEXT(I2)
J3	J3	=ADDRESS(ROW(I3),COLUMN(I3),ROW()-1)&" "& FORMULATEXT(I3)

 

[JenniferMurphy]

You could use the ADDRESS() function:

Of course. Why didn't I think of that? doh

But I don't understand your 3rd parameter. Why not a literal?

Take Function.xlsx
	N	O
9	3	$N$9: =1+2
10	7	N$10: =3+4
11	11	$N11: =5+6
12	15	N12: =7+8
MrExcel

Cell Formulas
Range	Formula
N9	N9	=1+2
O9	O9	=ADDRESS(ROW(N9),COLUMN(N9),1)&": "&FORMULATEXT(N9)
N10	N10	=3+4
O10	O10	=ADDRESS(ROW(N10),COLUMN(N10),2)&": "&FORMULATEXT(N10)
N11	N11	=5+6
O11	O11	=ADDRESS(ROW(N11),COLUMN(N11),3)&": "&FORMULATEXT(N11)
N12	N12	=7+8
O12	O12	=ADDRESS(ROW(N12),COLUMN(N12),4)&": "&FORMULATEXT(N12)