Linking Workbooks

kcdill

Board Regular
Joined
Mar 13, 2002
Messages
106
My cost estimating workbook consists of 5 worksheets (steel plate, pipe fittings, structurals etc.) containing lists of material. Each worksheet uses different formulas (lookup, IndexMatch, etc.) that call up values (unit weight, density etc.) found listed on the TablesSheet. My cost estimating workbook is currently about 1.3 megs and growing. About one half of the file size is attributable to the Table Sheet. We create several hundred cost estimates every year and I am using too much storage space. I want to reduce the estimating workbook file size by moving the TableSheet to a new TablesWorkbook. The new TablesWorkbook would be stored on the same server as the cost estimating workbook.
Question: How do I link all the different formulas in my estimating workbook to the new TablesWorkbook? Do I have to append each formula on by one? There must be a better way. Thanks in advance for your help.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
kcdill said:
My cost estimating workbook consists of 5 worksheets (steel plate, pipe fittings, structurals etc.) containing lists of material. Each worksheet uses different formulas (lookup, IndexMatch, etc.) that call up values (unit weight, density etc.) found listed on the TablesSheet. My cost estimating workbook is currently about 1.3 megs and growing. About one half of the file size is attributable to the Table Sheet. We create several hundred cost estimates every year and I am using too much storage space. I want to reduce the estimating workbook file size by moving the TableSheet to a new TablesWorkbook. The new TablesWorkbook would be stored on the same server as the cost estimating workbook.
Question: How do I link all the different formulas in my estimating workbook to the new TablesWorkbook? Do I have to append each formula on by one? There must be a better way. Thanks in advance for your help.

I maybe wrong here, but if the worksheets are presently in the same workbook, use Edit/Move or Copy Sheet to a new workbook and Excel should change the formulas to reflect the new workbook. Test this first. I've done this a few times and Excel always recognizes the different workbook.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Copy --> Paste Special .. Paste Links.
 

kcdill

Board Regular
Joined
Mar 13, 2002
Messages
106
Linked Workbooks Except Combo Boxes

Thanks in advance for the help. I moved my TablesSheet to a new workbook. Excel took care of the links like you said. However the one remaining problem is with all the ComboBoxes. The ListFillRange for each ComboBox is on the TablesSheet in the new WorkBook. Now the ComboBox drop down menus are blank. Is there a way to link the ComboBox on the Estimate Workbook to the ListFillRange in the Tables WorkBook?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

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
Top