How can I transfer formulas between 2 Excel workbooks?

[kjh4eyes]

I have two workbooks, call them Workbook A and Workbook B. Workbook A is a
template that I use to track monthly sales, cash flow, etc. Each month I
simply plug in the new dates on each worksheet. Worksheet B is the current
month's report. I added a page with new formulas to the template (Workbook
A). I now want to transfer that page to Workbook B. When I copy and paste the
page from Workbook A to Workbook B all of the formulas refer to worksheets in
Workbook B. I want to make those formulas refer to the worksheets in Workbook
A so I don't have to go back in and reenter data. I know there must be a way
to do this but I do not know what it is. Can someone out there help this
Excel novice?

 

[ryguy7272]

Right-click on the sheet tab, then Move or Copy > Move Selected Sheets to
Book > Create a Copy.


Regards,
Ryan---

 

[Niek Otten]

Save the workbook
Edit>Move or Copy Sheet, check Create a copy, New sheet
Save the new workbook
In the new workbook: Edit>Links>Change Source and select the new workbook

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I have two workbooks, call them Workbook A and Workbook B. Workbook A is a
| template that I use to track monthly sales, cash flow, etc. Each month I
| simply plug in the new dates on each worksheet. Worksheet B is the current
| month's report. I added a page with new formulas to the template (Workbook
| A). I now want to transfer that page to Workbook B. When I copy and paste the
| page from Workbook A to Workbook B all of the formulas refer to worksheets in
| Workbook B. I want to make those formulas refer to the worksheets in Workbook
| A so I don't have to go back in and reenter data. I know there must be a way
| to do this but I do not know what it is. Can someone out there help this
| Excel novice?

 

[kjh4eyes]

Ryan,

I did that already. The formulas still refer to the original worksheet.
For example:

Workbook A: Cell B4 in my worksheet called TOTALS has the following formula
='1'!F14
Workbook B: I want to transfer the formulas in TOTALS from workbook A to a
blank worksheet called TOTALS in workbook B.
When I copy and paste or move the page, Cell B4 in Workbook B has the
following formula ='[Worksheet A.xls]1'!F14
What I want it to do is say ='1'!F14 and calculate totals from worksheet B,
and not refer to worksheet B.

 

[kjh4eyes]

Thanks. That got it. I received and error message first but that was
because the tabs in the destination workbook had already been renamed for the
days of this month. When I went back and simply named the worksheets 1-27 it
worked. Sure wish I had asked in the forum sooner!