Concatentate a formula with a variable sheet name.
 

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

Concatentate a formula with a variable sheet name.
 

Don,

Does this do it

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

--

HTH

Bob Phillips

"Don Pistulka" wrote in message ...
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

Concatentate a formula with a variable sheet name.
 

Bob,

That's It.

Thanks

Don Pistulka
"Bob Phillips" wrote in message ...
Don,

Does this do it

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

--

HTH

Bob Phillips

"Don Pistulka" wrote in message ...
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