5.6 MB file, users having difficulties

beetlebug

New Member
Joined
Oct 9, 2007
Messages
25
I have a large model over 5MB that appears to have trouble upon saving. It gets hung up at around 40% calculation and is very slow when I do try to Save. The file itself has a few macros in it and the INDEX/MATCH formulas through it's 10+ worsheets. Is going to be a problem with the efficiency of the file no matter what I do?

This tool has to get out to several people to use and it works great! if only they could get it to run quickly (or quicker).

Any thoughts? Would it help to turn off calculation and then let it save (I assume it automatically starts calculating when you hit save as well?)

Thanks,
Rhonda
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
Hi,

Firstly you can set Excel so that it doesn't re-calc upon save but thats not really getting to the crux of the problem.

Is it essential that all the look ups are held within the spreadhseet as live formula, could you write code to calculate all the Lookups and then they will be stored as hard code, you also have more control over when they Lookup's re-calc and this will shrink the size of your file and also still allow all the other formula's to be set to automatic.

Hope this helps some.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,166
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
5MB is not that big. How long does it take to save? Which version of Excel is being used and can you give some examples of these INDEX/MATCH formulas?
 

beetlebug

New Member
Joined
Oct 9, 2007
Messages
25
5MB is not that big. How long does it take to save? Which version of Excel is being used and can you give some examples of these INDEX/MATCH formulas?

It ends up crashing and I have to kill Excel.. so it doesn't save entirely.. or if it does.. it's 5+ minutes. Here is an example of the formula:

=INDEX('ALL EXP - excl NR'!$A$4:$E$1145,MATCH($A10&$B10,'ALL EXP - excl NR'!$A$4:$A$1145&'ALL EXP - excl NR'!$C$4:$C$1145,0),4)

I have this in 10 worksheets, about 300 rows in each, and it references 4 different worksheets (above is just one example).

I can't write VBA code - or whatever it is that you all speak of.. It's beyond my scope of understanding! :(
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,166
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

That's a pretty inefficient formula in terms of calculation required. Can you add columns to combine the 'ALL EXP - excl NR'!$A$4:$A$1145 and 'ALL EXP - excl NR'!$C$4:$C$1145 column values to create a single key column to match on? Also, if you are using a fixed column, then there's no need to include the other 4 columns in the INDEX part:
Code:
=INDEX('ALL EXP - excl NR'!$D$4:$D$1145,MATCH($A10&$B10,'ALL EXP - excl NR'!$A$4:$A$1145&'ALL EXP - excl NR'!$C$4:$C$1145,0))
and if you use the same MATCH formula to return values from different columns in the table, you should store the MATCH in a separate column and then reference that value from the various INDEX formulas.
 

beetlebug

New Member
Joined
Oct 9, 2007
Messages
25
That's a pretty inefficient formula in terms of calculation required. Can you add columns to combine the 'ALL EXP - excl NR'!$A$4:$A$1145 and 'ALL EXP - excl NR'!$C$4:$C$1145 column values to create a single key column to match on? Also, if you are using a fixed column, then there's no need to include the other 4 columns in the INDEX part:
Code:
=INDEX('ALL EXP - excl NR'!$D$4:$D$1145,MATCH($A10&$B10,'ALL EXP - excl NR'!$A$4:$A$1145&'ALL EXP - excl NR'!$C$4:$C$1145,0))
and if you use the same MATCH formula to return values from different columns in the table, you should store the MATCH in a separate column and then reference that value from the various INDEX formulas.

OK, I'm following you somewhat:

1. Combining column values to create a single key column - not sure this is possible? Do you mean, to combine some items with a Concatanate formula and then use index/match on that one item with that one column? The problem is that the data sheet which is beign matched to, is something that will be replaced every month via an import process. I can't put formulas in there.

2. And to your other point, the reason I was using the other 4 columns in the INDEX part, I believe, was so I could use the same formula across a couple of columns, saving time.. but it's easy to change, I guess.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,166
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Re 1, yes that's what I meant. Can you not use a macro added to the "import process" (however that works)?.
Re 2, if you are using the same formula, then what you want is something like this:
In a cell, say C10, you have the MATCH part:
Code:
=MATCH($A10&$B10,'ALL EXP - excl NR'!$A$4:$A$1145&'ALL EXP - excl NR'!$C$4:$C$1145,0)
then to get the values from D and E columns in the table, you can use:
Code:
=INDEX('ALL EXP - excl NR'!D$4:D$1145,$C10)
=INDEX('ALL EXP - excl NR'!E$4:E$1145,$C10)
and having entered the first of those formulas you can simply copy it across and down as required due to the relative column references. It should be more efficient and may even remove the need for the key column mentioned in point 1.
 

beetlebug

New Member
Joined
Oct 9, 2007
Messages
25
Re 1, yes that's what I meant. Can you not use a macro added to the "import process" (however that works)?.
Re 2, if you are using the same formula, then what you want is something like this:
In a cell, say C10, you have the MATCH part:
Code:
=MATCH($A10&$B10,'ALL EXP - excl NR'!$A$4:$A$1145&'ALL EXP - excl NR'!$C$4:$C$1145,0)
then to get the values from D and E columns in the table, you can use:
Code:
=INDEX('ALL EXP - excl NR'!D$4:D$1145,$C10)
=INDEX('ALL EXP - excl NR'!E$4:E$1145,$C10)
and having entered the first of those formulas you can simply copy it across and down as required due to the relative column references. It should be more efficient and may even remove the need for the key column mentioned in point 1.

Rory,
Thanks a bunch! I am going to try this now and let you know how it works. So, separating the INDEX/MATCH function in separate cells like that will really help the processing time? I guess I'll find out..
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,166
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you use the same MATCH multiple times to retrieve a few columns for a given match row, then yes because you only have to do the row lookup once.
 

spcalan

Well-known Member
Joined
Jun 4, 2008
Messages
1,247
I have a file that is around 4mb, and it crashes 1-4 times.

I have 8 sheets with each sheet having 75 sumproduct formulas ( with 4 variables ) each.

I assume that is the reason for the slowness.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,489
Messages
5,601,986
Members
414,489
Latest member
Xlambda

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