Concatentate a formula with a variable sheet name.

[Don Pistulka]

Hi,



I am trying to change the link in worksheet formulas that are copied from one sheet to another by creating the formulas with the sheet name as a variable.

In "sheet2" is the formula:



="="&"'"&J1&"'"&"!"&"a1+10"



Cell J1 is the name of another sheet (i.e. Sheet1)



The formula results in:



='Sheet1'!a1+10



The results are as I expected, however it is text and not a formula. How do I get the results to act as a formula.



Don Pistulka

 

[Bob Phillips]

Don,

Does this do it

=INDIRECT("'"&J1&"'!a1")+10

--

HTH

Bob Phillips

Hi,



I am trying to change the link in worksheet formulas that are copied from one sheet to another by creating the formulas with the sheet name as a variable.

In "sheet2" is the formula:



="="&"'"&J1&"'"&"!"&"a1+10"



Cell J1 is the name of another sheet (i.e. Sheet1)



The formula results in:



='Sheet1'!a1+10



The results are as I expected, however it is text and not a formula. How do I get the results to act as a formula.



Don Pistulka

 

[Don Pistulka]

Bob,

That's It.

Thanks

Don Pistulka
Don,

Does this do it

=INDIRECT("'"&J1&"'!a1")+10

--

HTH

Bob Phillips

Hi,



I am trying to change the link in worksheet formulas that are copied from one sheet to another by creating the formulas with the sheet name as a variable.

In "sheet2" is the formula:



="="&"'"&J1&"'"&"!"&"a1+10"



Cell J1 is the name of another sheet (i.e. Sheet1)



The formula results in:



='Sheet1'!a1+10



The results are as I expected, however it is text and not a formula. How do I get the results to act as a formula.



Don Pistulka