reference this exact cell (x,y) of another sheet

Question

Please, imagine the following scenario. Document consists of 2 sheets: sheet1, sheet2. Sheet1 has 4 cells filled in. A1, A2, B1, B2. Bonjour, S'il vous plaît, Merci, De rien - all 4 cells are in French. Sheet2 has 4 cells filled in. A1 = googletranslate(Sheet1!A1). Next, it is dragged to autofill. So, we have nice autotranslation whatever a French guy enters on the Sheet1. The problem, however, exists. If a French guy deletes Sheet1!B1 or Sheet1!B2 then autofill can no longer reference Sheet1!B2 as the originally targeted cell does not exist anymore, Error(#Ref) is displayed and we have to again autofill the whole 9 yards.

The question: is there a way to fix a formula in Sheet2 cells to always point to its own x,y coordinate but on the Sheet1?

Answer 1

address allows you to specify a specific row, column and Sheet. If you substitute row() for the row number, and column() for the column number, then you will be addressing the exact cell co-ordinates as the location of the formula. Combine this with iferror to cover the case of a user deleting cell content on Sheet1, and you have a genuinely dynamic formula.

=iferror(googletranslate(indirect(address(row(),Column(),4,true,"Sheet1")),"fr","en"),)

Answer 2

Using offset, row(), column(), I've managed to achieve the goal

=googletranslate(OFFSET('Sheet1'!$A$1;ROW(A1)-1;COLUMN(A1)-1);"fr";"en")

the only downside is deleting A$1$ itself

Answer 3

Try with this formula:

=if(Sheet1!A1<>"";googletranslate(Sheet1!A1);)

in this way, even if all cells A1:B become empty, the formula will work without error.

Update

Based on your last specifications, this formula should work better, as deleting Sheet1!A1 would have no consequences:

=iferror(unique(googletranslate(Sheet1!A$1:A)))

And this is even better because it translates the whole A column without the need to drag down the cell containing the formula

=transpose(split(googletranslate(regexreplace(join("|";sort(Sheet1!A$1:A));"\|{2;}";"");"fr";"en");"|";0;1))