I would like to substitute the following formula (below) that has the "2006"
in the worksheet reference with either a variable name or a cell reference.
Below I have tried to explain this.

=SUM('[Jennifer PTO 2006.xls]Randy'!$C$100)
Explanation: This is the actually formula that I am trying to change, and
this one works. From the worksheet called Jennifer PTO 2006, I am using cell
reference C100 and carring that value over to another worksheet.

I would like to create a variable name for 2006, so that by changing the
value of a cell to 2007 the 2006 is replaced within the formula €“OR- just use
a cell reference to change this as well.

Something like: Refer to the sum(c4) in the first example or the
"carry_over" for the example 2.

=SUM('[Jennifer PTO, sum(c4).xls]Randy'!$C$100) -- where cell c4 has the
value of 2007

OR

=SUM('[Jennifer PTO, €œcarry_over€�.xls]Randy'!$C$100) €“ where carry_over has
the value of 2007

I am getting a syntax error REF#, but cannot find any help on how to insert
a variable name within a formula.

Any help is much appreciated.

Thanks.

Randy

Hi Randy,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"pappy" wrote in message ...
|
| I would like to substitute the following formula (below) that has the "2006"
| in the worksheet reference with either a variable name or a cell reference.
| Below I have tried to explain this.
|
| =SUM('[Jennifer PTO 2006.xls]Randy'!$C$100)
| Explanation: This is the actually formula that I am trying to change, and
| this one works. From the worksheet called Jennifer PTO 2006, I am using cell
| reference C100 and carring that value over to another worksheet.
|
| I would like to create a variable name for 2006, so that by changing the
| value of a cell to 2007 the 2006 is replaced within the formula -OR- just use
| a cell reference to change this as well.
|
| Something like: Refer to the sum(c4) in the first example or the
| "carry_over" for the example 2.
|
| =SUM('[Jennifer PTO, sum(c4).xls]Randy'!$C$100) -- where cell c4 has the
| value of 2007
|
| OR
|
| =SUM('[Jennifer PTO, "carry_over".xls]Randy'!$C$100) - where carry_over has
| the value of 2007
|
| I am getting a syntax error REF#, but cannot find any help on how to insert
| a variable name within a formula.
|
| Any help is much appreciated.
|
| Thanks.
|
| Randy
|
|