Excel 2007 : Linking to other WorkBOOKS - Overload

**ROB1963MAN** · 03-30-2012, 01:58 PM

Hello! I have a question here.

I have a MASTER sheet that is pulling in data from 22 other identically designed/formatted workBOOKS (not worksheets). It is an enormous amount of data to the point where the Master workbook contains approximately 3-Million formulas.

While the sheet does return a value for all the formulas, I question its integrity due to the sheer size of the 30-Megabyte Master workbook. I have audited in the past to see if it is producing accurate results, and have found discrepancy in the numbers whether the SLAVE workbooks were open, or closed.

My question is: Does excel have a limit of cells that can be linked to from a master sheet? I realize it may be because of PC-resources consumption by having all these files open at once, but I'm not sure if that is the reason, or a limitation in Excel (2007)

**eddyvilla** · 03-30-2012, 07:39 PM

hi Robi

i have encounter almost the same problem however my master sheet was not as overload like yours... what i've found is that you need to go over DATA and "Update conections" you should see all the links there. or if you have circular reference formulas... try checking the "iterative formula and set over 30,000. is what solved the problem in my case. Good Luck!

**FDibbins** · 03-31-2012, 07:38 PM

If you have that many references to external workbooks, perhaps check to see if your formulas are as simplified as possibe? If you are using if() statements in your master, consider modifying your slave workbooks to do them instead, so the master has less work to do

simple ='[file1.xlsx]sheet1'!$A$1 take up less resources than =if('[file1.xlsx]sheet1'!$A$1="","",'[file1.xlsx]sheet1'!$A$1), so do the if() in the slave. just a thought