1

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?

3 Answers 3

0

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

1
  • "the only downside is deleting A$1$ itself" Try my new formula
    – Daniele
    Jul 31 at 3:11
0

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))
2
  • Thank you for the idea! The problem is not an empty cell, but no longer existing cell. If a user deletes Sheet1:A1 and than type smth in the cell that got created instead of A1 the will be still an error
    – NKAT
    Jul 30 at 19:08
  • @NKAT said"the only downside is deleting A$1$ itself" Try my new formula
    – Daniele
    Jul 31 at 2:56
0

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"),)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.