Size of Excel file getting unmanagable...

[Bravo2003]

I've just tried to rebuild from scratch my Master File that I use for sales reporting..
The original was getting over 6MB and is so slow to process even the simplest of changes, and can crash my excel if I have other files open.

I haven't even started adding all the required formulas yet and only input half of the master data so far and the file is already 3MB!?

What creates such large data requirements?
I've checked for Last Cell contents on all sheets (CTRL - END) and that looks fine
I try to avoid formulas with full column checks (A:A - using only applicable row requirements A1:A987 etc.)

Only thing I can now think causing the issue is from using colour in my headers/columns?

Any suggestions to help keep the file manageable?

 

[RoryA]

SUMIFS is actually efficient with entire columns, so I'd suggest you just use:

=SUMIFS('2018Sales'!$BZ:$BZ,'2018 Sales'!$B:$B,$C3,'2018Sales'!$D:$D,"Result")

unless you have data below the dataset that could get picked up in error. If you do, use INDEX instead of INDIRECT:

=SUMIFS('2018Sales'!$BZ$1:INDEX('2018Sales'!$BZ:$BZ,A15_2018_Sales),'2018Sales'!$B$1:INDEX('2018Sales'!$B:$B,A15_2018_Sales),$C3,'2018Sales'!$D$1:INDEX('2018Sales'!$D:$D,A15_2018_Sales),"Result")

 

[craigexcel]

Could calculation possibly be set to Manual, with a Recalc done after the changes have been made?

 

[Bravo2003]

SUMIFS is actually efficient withentire columns, so I'd suggest you just use:

=SUMIFS('2018Sales'!$BZ:$BZ,'2018Sales'!$B:$B,$C3,'2018Sales'!$D:$D,"Result")

unless you have data below the dataset that could get picked up in error. Ifyou do, use INDEX instead of INDIRECT:

=SUMIFS('2018Sales'!$BZ$1:INDEX('2018Sales'!$BZ:$BZ,A15_2018_Sales),'2018Sales'!$B$1:INDEX('2018Sales'!$B:$B,A15_2018_Sales),$C3,'2018Sales'!$D$1:INDEX('2018Sales'!$D:$D,A15_2018_Sales),"Result")

Thanks Rory, the INDEX option has worked well so far,
I’ve managed to re-write about half my volatile INDIRECT formulasand I’ve had less instability from the file now… Just a lot more to go toensure I can crack this.

Thanks

 

[nej12]

My file is being slow and unresponsive too it is about 1,000 KB. The reason I joined this forum today was to research whether anyone else was having delayed responses when using active x comboboxes in 2013.

What I have is very basic, the combobox is linked to a dynamic named range (using offset formula), with about 20 items only.
User can type extra data into the selected item.
The code behind it is just a keydown to prevent arrow keys from flipping the users data by accident and a gotfocus to populate the listfillrange and dropdown, so user doesn't have to click the drop down arrow..
The combobox is linked to a cell in a separate sheet.

The unresponsive aspect is, all of sudden, when user types into combobox, you can't see what you're typing until user exits the combobox.
It's so annoying it makes me want to bang my head.
Also, it feels like the cursor hides as you type, which I hate. The mouse control isn't set to hide while typing for sure.

Any thoughts?
It would be appreciated.
I'm going to start a whole new thread for this topic in a few minutes.

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...t-away-very-slow-post5175079.html#post5175079

Please do not post the same question multiple times.
All replies should go to the linked thread