Concatentate a formula with a variable sheet name.

  • Thread starter Thread starter Don Pistulka
  • Start date Start date
D

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
 
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
 
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
 
Back
Top