Excel How to reference a cell then add an offset to its formula (if possible)

I am not sure if this possible or not, basically trying to reference a cell's formula then add an offset of 1 to it (the image below may explain it better).

I've tried formulatext > indirect > offset(ref,1,0) but it didn't seem to work.

offset direct doesnt work.

Thanks

You can do this quite easily, using VBA:

Sub test()
  Range("G1").AutoFill Destination:=Range("G1:G2"), Type:=xlFillDefault
End Sub

This will take the formula in "G1" and apply it over the range "G1:G2", which is the same as dragging the "G1" formula towards "G2".

You could use this formula:

=LET(f,$G$2, d,A3:H3,
formula,SUBSTITUTE(SUBSTITUTE(FORMULATEXT(f)," ",""),"=",""),
parts,TEXTSPLIT(formula,"+"),
SUM(INDEX(d,,COLUMN(INDIRECT(parts)))))

It expects a formula in G2 that includes multiple cell-references joined by a +-sign.

The formula excerpts the single cell-references and retrieves the column index of each cell reference. Column index is then used to return the single values of the row.

Since I noticed in your comments to Inkca (not in the question itself) you wanted to use offset because you wanted to be able to change the offset of rows from the referenced cells in your sum, I wanted to share this option as well:

=LET(shift, 1,
     b,TEXTSPLIT(FORMULATEXT(G1),{"+","="},,1),
SUM(OFFSET(INDIRECT(b),shift,)))

This way you can easily change the number of shift to change the offset.

You could also reference a cell that holds the shift value, for instance G2:

=LET(shift, G2,
     b,TEXTSPLIT(FORMULATEXT(G1),{"+","="},,1),
SUM(OFFSET(INDIRECT(b),shift,)))

Tip for future questions is to better formulate the goal of your question. Note the many questions as a result of the question itself.

Would OFFSET(B1,1,)+OFFSET(D1,1,) do the job?
Or slightly more expandable...
=OFFSET(B1,ROW()-1,)+OFFSET(D1,ROW()-1,)

I'm unsure what you mean by "Whatever is selected in G1 is then calc+1 in G2"