Move Value to ArbitraryCell, given ColumnName and RowNumber of cell with the value

[Bruce2]

How may one move a value to ArbitraryCell, say D17, given ColumnName, say M, and RowNumber, say 83, being the location of the cell with the desired data/formula/value?

In a spreadsheet that updates itself, suppose the following:
the Range of interest is in column M,;
within that Range the Value of interest is in row 83; and
cell D17 has the equivalent of “=+M83.”

Suppose also, that as a CertainVariable in the spreadsheet changes, the row number of the Value of interest will change. If this CertainVariable increases (or decreases) by i, the row number will increase (or decrease) by i for all values within the Range e.g. if i = 3, the new RowNumber would be 86. We would want the value in D17 to become the equivalent of “=+M86.”

How does one change the value in D17 from the equivalent of “=+M83” to the equivalent of “=+M86?.”

[JohnSUN]

=+OFFSET(M86;CertainVariable;0)?

[Bruce2]

Thank you. Awesome response.

[parsely]

You will have to write a macro to be that flexible. See the calc guide (help>user guide) for the setup, and how to execute the macro.
Then a macro to change the formula will run something like

sub change_formula()

Dim document as object
Dim oSheet, oCell as Object
Dim iRow, iColumn As Integer

Rem get access to the document

document = ThisComponent
Rem counting starts at 0
oSheet = document.sheets.getByIndex(0)  

Rem set iRow and iColumn to visual world 'C2'
iRow = instr("ABCDEFGHIJKLMNOP", "C")  
iColumn = 2

Rem  .getCellByPosition(column, rows)  counts from 0
oCell = oSheet.getCellByPosition(iRow - 1 , iColumn - 1) 
oCell.setFormula("=A1+A2")

Rem or oCell.setValue(1.23) or oCell.setString("this one")

end sub

Fill the cells A1 and A2, of an empty calc document, with some numeric value to check the code, and adapt accordingly.

[Bruce2]

I’ll do it. Thanks for the encouraging insight.

[Bruce2]

Parsley, yours is a great idea for me. Again, thank you for your aid.