Excel 2007 : Vlookup to Outside Workbooks - Huge File Size

[Rizzu]

I've run into another issue i can't wrap my head around. Ive created a spreadsheet with approximately 200 lines of data, using =VLOOKUP formulas to reference other worksheets. The issue here is that the file became about 55 megs, takes several minutes to open on our terrible work computers. So i had it in my mind to take the file, turn each worksheet into a workbook, and reference it that way to cut down on the file size of the main spreadsheet. My issue is that it's retaining the size because it's referencing those large external workbooks.

What i'm wondering is if anyone has any tips or tricks to setup a =vlookup system to reference external workbooks, without tying in the entire file size with it.

Example for Clarification:
Main File: <100 KB
One External Report: 8,000 KB
Formula:

=IFERROR(VLOOKUP($A2,'B:\Outbound\Schedules\PO Lookup\[Montreal Received.xlsx]Montreal Received'!$A:$AB,17,0),"")

Main Files New Size: >8,000 KB

Is there a work-around for this? Any help or advice would be more than appreciated, thank you!

[TMS]

How many rows/columns/cells have a formula like that?

Are you using the IFERROR to cater for blank cells?

Regards, TMS

[Rizzu]

How many rows/columns/cells have a formula like that?

Are you using the IFERROR to cater for blank cells?

Regards, TMS

Hey TMS, thanks for the reply. The spreadsheet i'm using has formulas very similar to that in exactly 4000 cells.

And you are correct, I am using the IFERROR formula to cater for blank cells.

[TMS]

I put that formula in exactly 4000 cells from C1 to AZ80 and saved the workbook. It came in at 26 kb.

So, what else is there? Lots of worksheets? Lots of formats? Conditional formatting?


Regards, TMS

[Rizzu]

I put that formula in exactly 4000 cells from C1 to AZ80 and saved the workbook. It came in at 26 kb.

So, what else is there? Lots of worksheets? Lots of formats? Conditional formatting?


Regards, TMS

The main file totals the combined file size of everything that =vlookup is referencing to. Like so:
http://img15.imageshack.us/img15/8154/exampleor.jpg

The main file has minimal conditional formatting and one worksheet.

[shg]

Rizzu, please don't quote whole posts -- it just clutters the forum. Use the REPLY button, not the QUOTE button.

Thanks.

[Rizzu]

Rizzu, please don't quote whole posts -- it just clutters the forum. Use the REPLY button, not the QUOTE button.

Thanks.

Hi Shg, thank you for the reply, sorry i'm unable to simply "reply" as i can't seem to find the button anywhere. I'm only able to "quote" or "multi-quote", which i believe both will cause me trouble. Is it possible it's only visible to more experienced users with a higher post count? I'm still kind of new to the forums, please see picture at the link below.

http://img198.imageshack.us/img198/3...eplybutton.jpg

[TMS]

If you page down to the bottom, do you see a box that you can type in and, underneath, buttons for Post Quick Reply and Go Advanced.

If you do, type in the box and press Post Quick Reply. It's what I normally do anyway.

Regards

[shg]

sorry i'm unable to simply "reply" as i can't seem to find the button anywhere.

How about a NEW POST button?