Excel Refer to Yet to Exist Worksheet

[Legendarim]

Dear,
thank you for taking the time to review my issue.

I am attempting to create an excel template for my sales team to use.

The first 5 columns establish a list of all our possible product ID (800+) (category/type/brand/specificities,...)
Then I created formulas which look for this ID in the worksheet named "Old Data"
And formulas which look for this ID in the worksheet named "New Data"
It then computes comparison of the Old Data and New Data for each product, in order to evaluate the sales, volume, margin,...

I want this first sheet (Sheet 1) to be locked and standard.

My Sales guy can then get Old Data from any time period, and New Data from any other time period and compare it using the template.

He would just have to copy the old data and the new data (which come in a standard way from our intranet) to my template, rename the worksheet as "Old Data" and "New Data" so that the template can compute what he wishes for.

For Example:
Sales guy want to compare the sales of the month of January to the month of February.
He downloads the data for the month of January and copy to the Template.
He downloads the data for the month of February and copy to the Template.
The template is now made of 3 worksheet:
-Sheet 1
-Sheet 2
-Sheet 3

Sheet 1's formulas have to refer to the sheet named "Old Data" and to the sheet named "New Data"
So if he changes the name of Sheet 2 to "Old Data" and the name of Sheet 3 to "New Data", the Sheet 1 will execute its formulas.

The problem is:
I cannot figure out how to lock the formulas in my Sheet 1 (Template) to a worksheet name that does not yet exist. Right now they are using the sheet name 'Old Data', but if I remove this sheet to make it a standard 1 sheet template, all my cells change to #REF!, and all my formulas as well.

Thank you for your help!

 

[Neil_Murray]

Use the Indirect function.
e.g. =INDIRECT("'New Data'!A1")
It will still give a #REF error, but will resolve itself once the sheet is created.

 

[Joe4]

Note that you can hide the #REF error like this:

=IFERROR(INDIRECT("'New Data'!A1"),"")

For older version of Excel that do not have IFERROR, it can be done. Just a little bit lonher:

=IF(ISERROR(INDIRECT("'New Data'!A1")),"",INDIRECT("'New Data'!A1"))

 

[Legendarim]

I used this formula, which does not seem to work:

=INDEX(IFERROR(INDIRECT("'OldData'!$M$15:$M$1000"),""),MATCH(E205,IFERROR(INDIRECT("'OldData'!$C$15:$C$1000"),""),0))

 

[Legendarim]

I used this formula, which does not seem to work:

=INDEX(IFERROR(INDIRECT("'OldData'!$M$15:$M$1000"),""),MATCH(E205,IFERROR(INDIRECT("'OldData'!$C$15:$C$1000"),""),0))

Figured it out, the right formula for this single issue is:

=IFERROR(INDEX(INDIRECT("'OldData'!$M$15:$M$1000"),MATCH(E205,INDIRECT("'OldData'!$C$15:$C$1000"),0)),"-")