Offsetting a formula

[ChristineJ]

I am trying to return what a formula would be if a variable number of rows and columns (shown in A2 and A3) were inserted above a cell containing a formula. Example:

Cell A2 = 2
Cell A3 = 3
Cell C3 contains the formula =D1+B2 that returns an amount:
Cell D3 contains =FORMULATEXT(C3) that returns =D1+B2

Is there a way to return the formula =F4+D5 as text in Cell E3. This is determined by "adding" 2 (from A2) to the letter of each cell reference and 3 (from A3) to the number of each cell reference?


Thanks!

 

[Peter_SSs]

Is it always just addition of cells?

ChristineJ_1.xlsm
	A	B	C	D	E
1				6
2	2	4
3	3		10	=D1+B2	=F4+D5
Sheet1

Cell Formulas
Range	Formula
C3	C3	=D1+B2
D3	D3	=FORMULATEXT(C3)
E3	E3	=LET(ind,INDIRECT(TEXTSPLIT(MID(D3,2,LEN(D3)),"+")),"="&TEXTJOIN("+",,ADDRESS(ROW(ind)+A3,COLUMN(ind)+A2,4)))

 

[ChristineJ]

Thanks for the reply. I am trying to enter a formula in a comment box that is "dynamic".

I am getting a #VALUE error - I have Office 365 but wonder if the LET function is not available. In any case, this is a bit complex for what I am doing since I have a number of formulas and they are not limited to addition. I will try another approach of creating the comment boxes after rows and columns are inserted above and to the left.

Appreciate all the help - I wish I could create such complex formulas! Christine

 

[Peter_SSs]

I am getting a #VALUE error - I have Office 365 but wonder if the LET function is not available.

It would more likely be the TEXTSPLIT function if there is one that you don't have. However, if you were missing any of the functions that I used in the formula then I would expect a #NAME? error, not #VALUE.
It is easy to check if you have the functions. In a blank cell type = and then start typing the function name. If you have the function it should appear as shown below

In any case, this is a bit complex for what I am doing since I have a number of formulas and they are not limited to addition. I will try another approach ..

OK, fair enough. Thanks for letting us know.