How to keep references valid, when cell does not exist yet

[MTSkibum]

For example I have Sheet 1

I have a formula in sheet 1
=Sheet2!A1

But Sheet 2 does not exist. It will not exist until I run a program to create the sheet and populate the data.

However after I run the program I get #ref


How can I keep the reference valid until I can get the sheet created?



Thanks,
Jay

[Sam Capricci]

you could put this before the formula ' that will deactivate it. Then when it exists do a find and replace and that may work.

[Ace_XL]

Use

=INDIRECT("Sheet2!A1")

[ragulduy]

=INDIRECT("Sheet2!A1")

[MTSkibum]

Is there a way to get indirect to work when explicitly stating the workbook?


=INDIRECT("'C:\Folder1\[Workbook1.xlsx]Sheet2'!$B$3")