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

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?

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

the only downside is deleting A\$1\$ itself

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

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))
``````
• 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 Jul 31 at 2:56

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