Use "master formula" in multiple workbooks?

feetimo

New Member
Joined
Mar 14, 2010
Messages
18
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?
 
I trust you will read through the code and make any corrections needed before trying it, like to the fPath variable, yes? ;)
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
It works! What's the required code to except a specific worksheet...all my workbooks have a "LOOKUP" sheet that contains lookup-type information. It would be good if the formula left this sheet alone.
 
Upvote 0
Add this and edit to suit:
Rich (BB code):
        'Copy in the formulas into each sheet
            For Each ws In ActiveWorkbook.Worksheets
                If ws.Name <> "Lookup" Then
                    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                    NewFormulaRNG.Copy ws.Range("E2:G" & LR)
                End If
            Next ws
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top