Excel worksheet freezes with insert/copy/paste

[Kimber]

The attached workbook is a mini version of the real one. The Exec Summary and dashboard sheets are created from data on 12 other worksheets. I deleted all but one to keep the file upload small. The report is updated monthly to show the most recent 12 months of data. All worksheets are part of the report, so the data is not on one worksheet. Each month, I copy last month's column and insert the copied cells and clear the entries and then I hide the month's column that is 13 prior to the current month. (I did not delete the hidden months in the blue tab. I then copy and paste (to move) the pink shaded cells at the bottom of each worksheet. Excel freezes and this process took me days last month. There must be a better way! Not every sheet has all of the locations listed on the dashboard.

Thank you to anyone who can help me!

 

[vletm]

Have You tried to set 'Calculation' to manually then NO NEED TO CALCULATE?

 

[Kimber]

Yes, I have tried that for this workbook.

 

[vletm]

Kimber
... why so long formulas?
I tested some ... You would test how do those work?

 

[vletm]

Kimber ... ouch
Maybe this version would be easier and more close for You needs.
Now You could 'copy/paste' new months easier ... and add date to row four!
I moved/modified 'some' calculations to left side ... You'll see Yourself.
There are few hint-texts.

 

[Kimber]

Kimber
... why so long formulas?
I tested some ... You would test how do those work?

Kimber ... ouch
Maybe this version would be easier and more close for You needs.
Now You could 'copy/paste' new months easier ... and add date to row four!
I moved/modified 'some' calculations to left side ... You'll see Yourself.
There are few hint-texts.

Thank you, vletm. I really like your version. I one question so far -- it seems you did not address the pink-shaded box at the bottom of the Speed Performance tab. That updated information needs to be displayed on the page each month.

Also, do you have any idea of why Excel freezes with this file? I've tried to clean it up, but it still freezes. Perhaps it's due to my formulas? And, to answer you question - I entered the formulas as I understand them. I did not know a more concise way to get the desired outcome. I have used this report for years without trouble.

 

[vletm]

Pink box: 'my version' has that pink pox too,
but now I checked that You have used those pink values ...
Which range averages are those?
or how different to [V29:V31] values? ... looks same values, different formulas!
my version uses these You have used these!
and my version looks this.

Still freezing?
Did You tested my version?
Did You uses 'my formulas'?
Did You duplicate new sheets from my version?
... Could You send it here? I could check it too?

 

[bosco_yip]

.....
Also, do you have any idea of why Excel freezes with this file? I've tried to clean it up, but it still freezes. Perhaps it's due to my formulas?....

1] Try to change your formula

From this :

=IFERROR(INDEX('Speed Performance'!V:V,MATCH($A3,'Speed Performance'!$B:$B,0)),"N/A")

Into this :

=IFERROR(INDEX('Speed Performance'!V$5:V$24,MATCH($A3,'Speed Performance'!$B$5:$B$24,0)),"N/A")

2] Avoid using the entire column as reference, that’s more than 1 million cells and will cause your computer slowing down.

Regards

 

[Kimber]

Good morning, vletm. I have been unable to answer your message; however, I will respond as soon as I have a chance to test your versions. Thank you so much for your help!

Pink box: 'my version' has that pink pox too,
but now I checked that You have used those pink values ...
Which range averages are those?
or how different to [V29:V31] values? ... looks same values, different formulas!
my version uses theseView attachment 40676 You have used these!
View attachment 40678 and my version looks this.

Still freezing?
Did You tested my version?
Did You uses 'my formulas'?
Did You duplicate new sheets from my version?
... Could You send it here? I could check it too?[/quote

 

[Kimber]

1] Try to change your formula

From this :

=IFERROR(INDEX('Speed Performance'!V:V,MATCH($A3,'Speed Performance'!$B:$B,0)),"N/A")

Into this :

=IFERROR(INDEX('Speed Performance'!V$5:V$24,MATCH($A3,'Speed Performance'!$B$5:$B$24,0)),"N/A")

2] Avoid using the entire column as reference, that’s more than 1 million cells and will cause your computer slowing down.

Regards

Thank you for the simple suggestion. I will definitely adjust my formulas.

 

[vletm]

Kimber
Did You test this version?