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