# 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?

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

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

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

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, 2022 at 19:08
• @NKAT said"the only downside is deleting A\$1\$ itself" Try my new formula Jul 31, 2022 at 2:56