Spreadsheet incredibly slow when moving tabs

[Roncondor]

Hi

I realize the title is not that helpful

I have a relatively large spreadsheet - around 7 MB - with a lots of calculations and 50+ tabs

It calculates fine and very quickly.

However, my problem is that if I move a tab or insert a blank line (even if that line does not impact any calculations), it can take 30 seconds.

It takes that long even if calculation is set to manual.

FYI - there are no VLOOKUP, OFFSET or INDIRECT functions in the spreadsheet or any circular references.

Any suggestions would be greatly appreciated!! THANK YOU!

 

[Joe4]

I have a relatively large spreadsheet - around 7 MB - with a lots of calculations and 50+ tabs

Any suggestions would be greatly appreciated!!

This could be a clue that you may be using the wrong tool for the job.
It sounds to me that you may really have a relational database (lots of data related to each other). If that is the case, a relational Database program, like Acess, SQL, MySQL, or Oracle would be better equipped for that.

 

[Roncondor]

This could be a clue that you may be using the wrong tool for the job.
It sounds to me that you may really have a relational database (lots of data related to each other). If that is the case, a relational Database program, like Acess, SQL, MySQL, or Oracle would be better equipped for that.

Thanks, but it needs to be in excel

It is not a database - it is a model to value the construction, financing, operation, and eventual sale of property consisting of 2 hotels; 2 apartment buildings; and a parking facility.

 

[Joe4]

Then why so many tabs and formulas?
What kind of formulas are they?
What do they look like?

 

[Roncondor]

Then why so many tabs and formulas?
What kind of formulas are they?
What do they look like?

I need 12 years of monthly cash flows for around 200 different line items for each of the 5 properties

Most of the tabs are reports.

 

[Joe4]

I need 12 years of monthly cash flows for around 200 different line items for each of the 5 properties

Most of the tabs are reports.

That really sounds like a database to me...

Perhaps this would be a good candidate for Power Pivots (which allows you to do some data grouping/summarizing).
I think that it handles large groups of data and summarizing better. I don't use it myself, but have seen some people do some pretty advanced stuff with it.

 

[Roncondor]

I CALCULATE all of the cash flows!

 

[Joe4]

You must remember, while you are very familiar with this problem because it is in front of you, all that we have to go on is what you have provided to us in this thread, and you have provided very little detail.
Without detailed information, all we can give you is general answers. Here is a link which gives some good tips on how to ask a good, complete question: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

Its obvious that how you are currently using Excel isn't working for you, so you need to investigate alternative ways of doing it in Excel, or alternative solutions.
I have given you a few suggestions (inside out outside of Excel), but it doesn't sound like you considered any of them. So without any more detailed information, there is probably not much more I can do for you.