Select the cell that contains the formula you want to move. Click Home > Cut (or press Ctrl + X). Select the cell you want the formula to be in, and then click Paste (or press Ctrl + V). Verify that the cell references are still what you want.
Press F2 (or double-click the cell) to enter the editing mode. Select the formula in the cell using the mouse, and press Ctrl + C to copy it. Select the destination cell, and press Ctl+V. This will paste the formula exactly, without changing the cell references, because the formula was copied as text.
When I call a spreadsheet function, say int(f2)
, the function operates on the value in the cell. If cell("F2")
contains 3.14159, the result would be 3.
But when I call a different type of function — for example: row(f8)
— the function takes the cell reference, and not the value, in this case, returning 8.
How do I get my custom function to work with the reference, rather than the value?
I can pass a string, and use getRange()
, but, if I move or update the cells on the sheet, the strings won't change.
Really simple example:
function GetFormula(cellname) {
return SpreadsheetApp.getActiveSheet().getRange(cellname).getFormula();
}
With this in my sheet's code, I can retrieve the formula in C4 like this: =GetFormula("C4")
But, this argument is a string, and I would rather pass a cell reference. A somewhat more complicated issue requires the calling cells to update when copied and pasted.
Any ideas?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With