I have a template worksheet that looks at another worksheet to get the
answers to the formulas in the template. Is there a way to update the
formulas to have it look at a different worksheet, without entering the
information for every column? Here is an example of the formula:

=IF('[Legacy]11657g200'!$AG2="Checked","X","")

I have used the Macro Recorder to do this, but I must enter the destination
path for every column. Does somebody know of a way to do this and only enter
the destination path only once? Below is a copy of the VB code from the
Macro.

Range("A2").Select
ActiveCell.FormulaR1C1 = "=Legacy!RC2"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=Legacy!RC5"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Legacy!RC3"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(Legacy!RC9,30)"
Range("E2").Select
ActiveCell.FormulaR1C1 = _

"=IF(Legacy!RC19=""Checked"",""X"",IF(Legacy!RC19= ""Indeterminate"",""I"",""""))"
Range("F2").Select
ActiveCell.FormulaR1C1 = _

"=IF(Legacy!RC20=""Checked"",""X"",IF(Legacy!RC20= ""Indeterminate"",""I"",""""))"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=Legacy!RC22"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IF(Legacy!RC33=""Checked"",""X"","""")"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(Legacy!RC34=""Checked"",""X"","""")"
Range("A2:I2").Select
Selection.AutoFill Destination:=Range("A2:I1499"), Type:=xlFillDefault
Range("A2:I1499").Select

Range("A1500").Select
ActiveCell.FormulaR1C1 = "=Hierarchy!R[-1498]C2"
Range("B1500").Select
ActiveCell.FormulaR1C1 = "=Hierarchy!R[-1498]C5"
Range("C1500").Select
ActiveCell.FormulaR1C1 = "=Hierarchy!R[-1498]C3"
Range("D1500").Select
ActiveCell.FormulaR1C1 = "=LEFT(Hierarchy!R[-1498]C9,30)"
Range("E1500").Select
ActiveCell.FormulaR1C1 = _

"=IF(Hierarchy!R[-1498]C19=""Checked"",""X"",IF(Hierarchy!R[-1498]C19=""Indeterminate"",""I"",""""))"
Range("F1500").Select
ActiveCell.FormulaR1C1 = _

"=IF(Hierarchy!R[-1498]C20=""Checked"",""X"",IF(Hierarchy!R[-1498]C20=""Indeterminate"",""I"",""""))"
Range("G1500").Select
ActiveCell.FormulaR1C1 = "=Hierarchy!R[-1498]C22*Hierarchy!R[-1498]C25"
Range("H1500").Select
ActiveCell.FormulaR1C1 =
"=IF(Hierarchy!R[-1498]C33=""Checked"",""X"","""")"
Range("I1500").Select
ActiveCell.FormulaR1C1 =
"=IF(Hierarchy!R[-1498]C34=""Checked"",""X"","""")"
Range("A1500:I1500").Select
Selection.AutoFill Destination:=Range("A1500:I3000"), Type:=xlFillDefault
Range("A1500:I3000").Select
ActiveWindow.ScrollRow = 1
Range("A2").Select
--
RedFive