Update Formulas

R

RedFive

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
 
D

Duke Carey

Why can't you do a search & replace? If you wanted to switch the link from a
sheet named Current to another sheet named Legacy, press Ctrl-h to bring up
the replace dialog

Enter Current! into the "Find what" box and Legacy! into the "Repalce with"
and click Replace All

RedFive said:
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
Click to expand...
 
R

RedFive

When I tried this, it wanted me to input the destination source for the new
values for each individual cell (about 24000.) Not very effective.
--
RedFive


Duke Carey said:
Why can't you do a search & replace? If you wanted to switch the link from a
sheet named Current to another sheet named Legacy, press Ctrl-h to bring up
the replace dialog

Enter Current! into the "Find what" box and Legacy! into the "Repalce with"
and click Replace All

RedFive said:
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
Click to expand...
Click to expand...
 
S

Spiky

If the formula doesn't change other than workbook name, you can just
use Update Links in the Links dialog box to change that. Best to have
all the affected workbooks open when you do this, that should make the
calculations fast and automatic.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify range in VBA 9
Creating Pivot Table with Macro using Multiple Sheets 0
fill range with formula 3
Checkbox linked to calcs 1
Checkbox linked to calcs 0
Error 2007 0
Macros in Excel 2
Selection AutoFill, Excel 2000 & 2003 1

Top