Return an "offset formula" to a cell

[ChristineJ]

Is there a way to return an "offset formula" to a cell - A4 in this example?

Cell A1 value = 3 (number of rows to add - can vary)
Cell A2 value = 5 (number of columns to add - can vary)
Cell A3 contains any formula, such as =SUM(H5:K8) - C2 * D4
Cell A4 should return the following formula: =SUM(M8:P11) - H5 * I7

This would be the formula if three rows were inserted above row 3 and 5 columns were inserted to the left column A, although I do not actually want to insert the rows or columns. I just want to know what the formula would be if I did. Thanks!

 

[James006]

Hi,

You could test :
=SUM(OFFSET(H5,A1,A2):OFFSET(K8,A1,A2)) - OFFSET(C2,A1,A2) * OFFSET(D4,A1,A2)

 

[ChristineJ]

Hi,

You could test :
=SUM(OFFSET(H5,A1,A2):OFFSET(K8,A1,A2)) - OFFSET(C2,A1,A2) * OFFSET(D4,A1,A2)

Thanks for the reply.

However, I actually want this formula to appear in cell A4 (not the result of the formula): =SUM(M8:P11) - H5 * I7

I'd like A4 to show the formula in A3, but as if 3 rows and 5 columns had been inserted. C

 

[ExceLoki]

this solution is only for the above formulas, if you put in a different formula into b3, then this would have to be edited.
B5 & B6 are essentially the same, other than using let function
-----------------------

Return an offset formula to a cell.xlsx
	A
1	3
2	5
3	0
4	SUM(M8:P11) - H5 * I7
5	=SUM(M8:P11)-H5*I7
6	=SUM(M8:P11)-H5*I7
Sheet1 (2)

Cell Formulas
Range	Formula
A3	A3	=SUM(H5:K8)-C2*D4
A5	A5	=SUBSTITUTE(TEXTJOIN("",,LEFT(FORMULATEXT(A3),5),ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A3),6,2)))+$A$1,COLUMN(INDIRECT(MID(FORMULATEXT(A3),6,2)))+$A$2),MID(FORMULATEXT(A3),8,1),ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A3),9,2)))+$A$1,COLUMN(INDIRECT(MID(FORMULATEXT(A3),9,2)))+$A$2),MID(FORMULATEXT(A3),11,2),ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A3),13,2)))+$A$1,COLUMN(INDIRECT(MID(FORMULATEXT(A3),13,2)))+$A$2),MID(FORMULATEXT(A3),15,1),ADDRESS(ROW(INDIRECT(RIGHT(FORMULATEXT(A3),2)))+$A$1,COLUMN(INDIRECT(RIGHT(FORMULATEXT(A3),2)))+$A$2)),"$","")
A6	A6	=LET(formula,FORMULATEXT(A3),SUBSTITUTE(TEXTJOIN("",,LEFT(formula,5),ADDRESS(ROW(INDIRECT(MID(formula,6,2)))+$A$1,COLUMN(INDIRECT(MID(formula,6,2)))+$A$2),MID(formula,8,1),ADDRESS(ROW(INDIRECT(MID(formula,9,2)))+$A$1,COLUMN(INDIRECT(MID(formula,9,2)))+$A$2),MID(formula,11,2),ADDRESS(ROW(INDIRECT(MID(formula,13,2)))+$A$1,COLUMN(INDIRECT(MID(formula,13,2)))+$A$2),MID(formula,15,1),ADDRESS(ROW(INDIRECT(RIGHT(formula,2)))+$A$1,COLUMN(INDIRECT(RIGHT(formula,2)))+$A$2)),"$",""))

 

[ChristineJ]

this solution is only for the above formulas, if you put in a different formula into b3, then this would have to be edited.
B5 & B6 are essentially the same, other than using let function
-----------------------

Return an offset formula to a cell.xlsx
	A
1	3
2	5
3	0
4	SUM(M8:P11) - H5 * I7
5	=SUM(M8:P11)-H5*I7
6	=SUM(M8:P11)-H5*I7
Sheet1 (2)

Cell Formulas
Range	Formula
A3	A3	=SUM(H5:K8)-C2*D4
A5	A5	=SUBSTITUTE(TEXTJOIN("",,LEFT(FORMULATEXT(A3),5),ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A3),6,2)))+$A$1,COLUMN(INDIRECT(MID(FORMULATEXT(A3),6,2)))+$A$2),MID(FORMULATEXT(A3),8,1),ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A3),9,2)))+$A$1,COLUMN(INDIRECT(MID(FORMULATEXT(A3),9,2)))+$A$2),MID(FORMULATEXT(A3),11,2),ADDRESS(ROW(INDIRECT(MID(FORMULATEXT(A3),13,2)))+$A$1,COLUMN(INDIRECT(MID(FORMULATEXT(A3),13,2)))+$A$2),MID(FORMULATEXT(A3),15,1),ADDRESS(ROW(INDIRECT(RIGHT(FORMULATEXT(A3),2)))+$A$1,COLUMN(INDIRECT(RIGHT(FORMULATEXT(A3),2)))+$A$2)),"$","")
A6	A6	=LET(formula,FORMULATEXT(A3),SUBSTITUTE(TEXTJOIN("",,LEFT(formula,5),ADDRESS(ROW(INDIRECT(MID(formula,6,2)))+$A$1,COLUMN(INDIRECT(MID(formula,6,2)))+$A$2),MID(formula,8,1),ADDRESS(ROW(INDIRECT(MID(formula,9,2)))+$A$1,COLUMN(INDIRECT(MID(formula,9,2)))+$A$2),MID(formula,11,2),ADDRESS(ROW(INDIRECT(MID(formula,13,2)))+$A$1,COLUMN(INDIRECT(MID(formula,13,2)))+$A$2),MID(formula,15,1),ADDRESS(ROW(INDIRECT(RIGHT(formula,2)))+$A$1,COLUMN(INDIRECT(RIGHT(formula,2)))+$A$2)),"$",""))

Wow - very impressive! Both work. I had no idea of the complexity of what I was asking! Thank you. C

 

[ExceLoki]

glad to help
there might be a shorter way to condense the formula, but this way does work...