Referencing a tab NUMBER rather than the tab NAME in a formula
 

I'm looking for a NON-VBA solution here.

Say I have an Excel spreadsheet with two tabs, named "Source Data" and
"Results". When I look at the VPAProject screen of this spreadsheet I see
the two tabs of the spreadsheet listed as objects:
Sheet1 (Source Data)
Sheet2 (Results)

On the spreadsheet tab "Source Data" I have the following data:
cell A1: 645
cell A2: 785

On the Results tab I have the following formula somewhe
"=+'Source Data'!A1+'Source Data'!A2"

In the above formula (not in some VBA routine), is there a way to reference
the underlying VBA sheet NUMBER, rather than the NAME "Source Data"?

Something like: "=+'Sheet1'!A1+'Sheet1'!A2"
(Obviously, I've tried this syntax without success).

The REASON for this is that my company has truly huge budget spreadsheets
(80 to 112 Meg) that uses many, many Indirect formulas. These are necessary
because the individual uses are allowed to add Project tabs, where the tab
name can be anything they want. Formulas elsewhere use the data on this
tabs, so formulas have to be "built up" from predefined pieces, using
Indirects. Indirects, however, are volative functions tha use a lot of
memory and increase calculation time. If, OTOH, we could use sequential tab
NUMBERS in the formulas, rather than the tab NAMES, we could replace many of
the Indirects with specific formulas.

TIA.

Referencing a tab NUMBER rather than the tab NAME in a formula
 

That name, i.e., Sheet1, is what is known as the codename and it isn't
accessible via worksheet functions

"Booknookoc" wrote:

I'm looking for a NON-VBA solution here.

Say I have an Excel spreadsheet with two tabs, named "Source Data" and
"Results". When I look at the VPAProject screen of this spreadsheet I see
the two tabs of the spreadsheet listed as objects:
Sheet1 (Source Data)
Sheet2 (Results)

On the spreadsheet tab "Source Data" I have the following data:
cell A1: 645
cell A2: 785

On the Results tab I have the following formula somewhe
"=+'Source Data'!A1+'Source Data'!A2"

In the above formula (not in some VBA routine), is there a way to reference
the underlying VBA sheet NUMBER, rather than the NAME "Source Data"?

Something like: "=+'Sheet1'!A1+'Sheet1'!A2"
(Obviously, I've tried this syntax without success).

The REASON for this is that my company has truly huge budget spreadsheets
(80 to 112 Meg) that uses many, many Indirect formulas. These are necessary
because the individual uses are allowed to add Project tabs, where the tab
name can be anything they want. Formulas elsewhere use the data on this
tabs, so formulas have to be "built up" from predefined pieces, using
Indirects. Indirects, however, are volative functions tha use a lot of
memory and increase calculation time. If, OTOH, we could use sequential tab
NUMBERS in the formulas, rather than the tab NAMES, we could replace many of
the Indirects with specific formulas.

TIA.