Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Update Formulas
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Update Formulas
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" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Update Formulas
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Update Formulas
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up formulas to update graphs dynamically | Charts and Charting in Excel | |||
How do I mass update formulas? | Excel Discussion (Misc queries) | |||
Formulas will not update! | Excel Discussion (Misc queries) | |||
Global criteria update for formulas? | Excel Discussion (Misc queries) | |||
Formulas across worksheets do not update | Excel Worksheet Functions |