I have two sheets, Sheet1 and Sheet2, in Sheet1 I want to copy the contents of cell A1 from Sheet2 which I’m successfully doing with =Sheet2!A1. The issue is that Sheet2 may or may not be there, in other words, the Excel document will always contain only Sheet1 and Sheet2 will at some point copied from other Excel document.
How can I make the following code stays in Sheet1 so when Sheet2 is created or copied the contents from cell A1 in Sheet2 will be shown in Sheet1?
=Sheet2!A1
Right now, if I delete Sheet2 I get =#REF!A1 and if I re-create it, the code is not recognized, it stays as =#REF!A1 and the cell contents do not show in Sheet1.
>Solution :
Use INDIRECT and IFERROR:
=IFERROR(INDIRECT("Sheet2!A1"),"")
Note that INDIRECT is volatile and should be used sparingly or avoided if possible.