Volatile or something else? Super slow...

[phpolicylady]

Howdy all,

I have a spreadsheet with multiple links to named references to other files loaded on an internal portal. They look a bunch like this:
='http://portal.company.com/sites/pla...nts/2014-15_Action_Plans.xlsm!'NAMEDREFERENCE

Each department has 40-50 namedreferences uniquely their own that I pull in a consolidated spreadsheet and run reports for the executives. There are approximately 350 rows of data only and fewer than 75 columns of data in all.

Based on the namedreferences data, I perform several (10-15) IF and COUNTIF functions for each row (each department plan). There are also a few (3-5) VLOOKUPs and nested IFs.
Additionally, I have separate sheets that use VLOOKUPs to present the data appealingly to the executives.

My concern is the speed with which this pulls...I realize that excel needs to update the links each time to ensure the most up-to-date information, but 5 minutes to open is unacceptable...and my entire computer is stalled during the whole process...thus supporting my online shopping habit, but not getting anything done.

I have read about volatile formulae, but I'm not sure if the number I have would constitute the lag time I see.

I'm so close to ditching the whole thing in favor of Access, but I was able to do this last year and the first half of this year with much faster speeds. I recently upgraded to Windows 8 and Office 2013, and these reports are the only ones where I find this sluggishness.

I have some VBA skills, but I really just copy what I find on forums like this one and tailor them to my needs Would an array or VBA help me at all? I need to be able to have others access these reports while I'm away on vacation, and I just can't stand giving them a lugging behemoth like this one is currently.

Thanks,
Rachel

 

[bobhc]

I'm so close to ditching the whole thing in favor of Access,

I would go with Access with what you are doing and trying to achieve, you are trying to pull a lot of data and many things could be contributing to the 5 minute opening, with Access you could split your Excel sheet into smaller departments/Management modules and then auto Inport in to Access and build the required tables there.

thus supporting my online shopping habit, but not getting anything done.

yep, that would lead to a might lag in serious work.....


.

 

[phpolicylady]

Thanks a bunch...I was fairly certain I would have to break down and do that, but I still wonder why it was fine for 18 months on older excel and windows...I feel like I must not be remembering something I did that made the whole thing angry.

Off to learn how to do calculations in Access...wish me luck!

 

[bobhc]

Off to learn how to do calculations in Access..

much the same as Excel just small (ish) differences.

You may find that cell references and named ranges in the update Excel may be some of the cause because of the potential sheet size in new Excel editions.


.

 

[NARAYANK991]

Hi Rachel ,

I am not able to understand the exact layout of your workbook ( the spreadsheet you refer to ).

1. Does it have several worksheets or does it have just one ?

2. The external references that you mention - are they all concentrated in one worksheet , or are they spread over several worksheets ?

3. The external references - are they all pointing to one external workbook , or do they point to several external workbooks ?

4. In your workbook , is the setting on automatic update of the external links when your workbook is opened ?

When you are designing an application , how well it is structured decides how well it performs ; shifting to Access is not necessarily going to result in better performance , if the problem lies elsewhere.

Narayan