Use "master formula" in multiple workbooks?

[feetimo]

I have multiple workbooks (.xls files) saved in a folder that each represent a seperate "recipe" or machine setup. Each workbook may contain several worksheets that are revisions of the given recipe. The format of the worksheets is as follows (roughly):

Columns A-D: User inputs
Columns E-G: Computer-generated outputs using formulas on the inputs.
...each worksheet containing approximately 200 rows of input/output data.

Currently the formulas are just that...formulas in each cell of each worksheet and workbook. A mess when mistakes/updates are needed for the formulas!

My questions:
1) Is there a way to keep the formulas in a separate master file/ worksheet/even macro if need be...remember, though, this is across worksheets AND workbooks (different .XLS files)--So that if a change is needed to the column "E" formula, for example, that it will be reflected in all workbooks as they are opened.
2) Seeing as the output cells will be transferred to an external system via .CSV or similar, is there a better application for the job?

 

[jbeaucaire]

Unless you want to open all 200 workbooks and install a Workbook_Open macro into each one, I would imagine you will need to create a macro and install it in your PERSONAL.XLS file so it is available all the time.

Or put it in a .XLA workbook and install it as an addin...another option.

Anyway, in your macro you can copy the "key formulas" from a template workbook that is always stored in the same place and paste them into the active workbook in the appropriate columns. This macro could be attached to a keyboard shortcut so it would be easy to initiate an "update" on any sheet you open.

========
Another options is to create a macro in your template file where you want to store these formulas, this macro is designed to silently run through all the workbooks in a folder inserting the updated formulas all at once. This might be preferable since you can then use the sheets normally with no further updating until you change something again.

 

[ravishankar]

HI
Another possiblity is to use the macro from the link
http://www.mrexcel.com/forum/showthread.php?t=455588
(change for h = 1 to 10 to for h = 1 to 4)
to consolidate all 200 files (for a given sheet) and apply the New formula to columns E to H.
Ravi

 

[xenou]

How many workbooks are involved? Is there only one sheet in each "child" workbook?

ξ

 

[feetimo]

Unfortunately, no...as each sheet represents a revision of the "recipe" that each workbook is for, some have just one where some have quite a few. However, there is only one sheet in each workbook that represents the active revision. So, going forward, the obsolete revisions could be left alone and only the first tab in each workbook used to move forward with.

 

[jbeaucaire]

Let us know if Ravi's macro works for you, after you make the suggested code adjustments.

 

[feetimo]

The macro seems to be thrown by the multiple sheets in each workbook. It assumes one sheet, called "Sheet1". A dialog box opens asking which sheet should be used to "Update Values From", but the macro ends up looping at this dialog box.

 

[jbeaucaire]

Try this one, perhaps easier to read and edit:

Option Explicit

Sub UpdateFormulas()
'JBeaucaire   3/18/2010
'Open all xls files in a folder, update the formulas IN E:G on every sheet
Dim fPath As String, fName As String, LR As Long
Dim NewFormulaRNG As Range, ws As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'The range of cells that will serve as the master formulas
    Set NewFormulaRNG = ThisWorkbook.Sheets("Sheet1").Range("E2:G2")
'Path to stored files
    fPath = "C:\2010\"
'create a list of files in that folder
    fName = Dir(fPath & "*.xls")

'Loop through files one at a time
    Do While Len(fName) > 0
        'open the file
            Workbooks.Open fPath & fName
        'Copy in the formulas into each sheet
            For Each ws In ActiveWorkbook.Worksheets
                LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                NewFormulaRNG.Copy ws.Range("E2:G" & LR)
            Next ws
        'Save and close the updated file
            ActiveWorkbook.Close True
        'ready the next filename
            fName = Dir
    Loop

Set NewFormulaRNG = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[ravishankar]

Hi
if the sheet names are same across all files, it can be included in the macro to replace sheet1.
RAvi

 

[feetimo]

No luck, Ravi...the sheet names are the revision dates, like "3-16-2010", so they are semi-unique...

Jbeaucaire, I'll try the revised macro you posted tomorrow when I get to work.