Small Excel file but extremely slow

[GrumpyChi]

Hi,

I have an Excel file which is 1,511 KB in size. Whenever I or anyone opens this file, it takes a very long time to load. Unless we turn on Manual calculations, it is impossible to navigate around the workbook without it getting stuck on "Calculation" which takes minutes for each and every click.

I have no idea why it is doing this. There are no macro's in the workbook.

The steps I have already done are removing objects from the workbook (the only objects were a couple of screenshots), ticked "Disable hardware graphics acceleration" under advanced options. It did not help.

Can anyone help please or have any other tips?

Thank you

 

[SanjayGMusafir]

Unless we turn on Manual calculations

It's all about multiple lookups in a file. I already explained in a thread. Best option is what you have already done - SHIFT to MANUAL Calculations

Calculating threads problem

Hello everyone, I notice a message at the bottom of the excel sheet stating calculating threads even when the code is not running. When I try to insert a sheet or edit any particular sheet, it is calculating threads and takes too much time to stop. I tried closing the workbook and re-opened...

www.mrexcel.com

 

[GrumpyChi]

I have just read online that the Indirect formula could be a cause of this issue. Can anyone suggest an alternative to the below formula?

=IFERROR(IF(A2="","check",IF(MATCH(1,(O2=INDIRECT(R2&"!D:D"))*(A2=INDIRECT(R2&"!a:a")),0)>0,"ok","check")),"check")

 

[GrumpyChi]

It's all about multiple lookups in a file. I already explained in a thread. Best option is what you have already done - SHIFT to MANUAL Calculations

Calculating threads problem

Hello everyone, I notice a message at the bottom of the excel sheet stating calculating threads even when the code is not running. When I try to insert a sheet or edit any particular sheet, it is calculating threads and takes too much time to stop. I tried closing the workbook and re-opened...

www.mrexcel.com

Also, shifting to manual calculations won't work. I need it to be a live file.

 

[SanjayGMusafir]

Actually this is the thread -

Excel slows down. With alot of xlookup formulas

Hi any remedy to make my excel faster? This is some sample of my excel formulas =xlookup = b5&c2, NAME&DATE, LEAVE,"") =xlookup = b6&c2, NAME&DATE, LEAVE,"") =xlookup = b7&c2, NAME&DATE, LEAVE,"") =xlookup = b8&c2, NAME&DATE, LEAVE,"") Then drag the formula until =xlookup = b55&c2...

www.mrexcel.com

 

[SanjayGMusafir]

=IFERROR(IF(A2="","check",IF(MATCH(1,(O2=INDIRECT(R2&"!D:D"))*(A2=INDIRECT(R2&"!a:a")),0)>0,"ok","check")),"check")

Yes Indirect formula can cause such issue

But if I'm right, better create a new thread to improve the formula. This way you will derive better help on it...

Still hope it catches eye of someone who is expert on it.

 

[offthelip]

Apart from using indirect your formula is also addressing complete columns e.g. "!D:D" that will slow down the calculation , can you change that to addressing a specific range, which could be a named range that calculates the last row.
Also how many different sheets might you get in R2?? If it is not very many you could get rid of the indirect by checking each sheet in the formula and then selecting which "if check" by checking whether the value in r2 is the same as your sheet check name

 

[GrumpyChi]

Apart from using indirect your formula is also addressing complete columns e.g. "!D:D" that will slow down the calculation , can you change that to addressing a specific range, which could be a named range that calculates the last row.
Also how many different sheets might you get in R2?? If it is not very many you could get rid of the indirect by checking each sheet in the formula and then selecting which "if check" by checking whether the value in r2 is the same as your sheet check name

Hi there, that's a good point I can change D:D to look at a specific number of rows which wouldn't be more than say 20 for each tab. The R2 cell could be up to 30 separate tabs. I wouldn't know how to re-write this formula so I will try to create a new thread to look at this.

 

[awoohaw]

for the multiple sheets there is also online help on that as well.
You can look at this:

Create a reference to the same cell range on multiple worksheets - Microsoft Support

A reference that refers to the same cell or range on multiple sheets is called a 3-D reference. Use a 3-D reference to consolidate data in different worksheets.

support.microsoft.com

But it is something like this:
the sheets must all be together in your workbook.

FirstSheet:LastSheet!R2