Formula Efficiency - Advice please (perhaps useful to many users)

[Pete_Bristol]

Hello all,

Please can anyone offer advice at to whether this is a good or bad habit.

I have a number of workbooks that pass data between themselves. Rather than have links embedded within the myriad of different worksheets I have created a single sheet of "IMPORTED" values which the formulas in the various worksheets then pick up from. This keeps all the links together and are easily auditable (or changeable) etc. so on the whole I think is probably a good habit. (point 1)

Extending this idea a little, I have also created two separate tabs within each workbook called "EXPORT_LINKS" and "IMPORT_LINKS" - maybe a little duplication of formulas but it makes understanding the information flow between the workbooks easy to follow. (point 2)

Because most users have no idea about array formulas and to add extra security against accidental changes etc. I have started to pass these ranges around the various workbooks as arrays i.e. the IMPORT_LINKS worksheet in one workbook might reference a range of {'some_other_workbook'!A1:P1000} in another. This "block of data" is compact and not changed by your average user.

However, I'm beginning to think that the "benefits" of this are being outweighed by speed as the workbook re-calculation times are getting longer and also that the "calculate" message is constantly displayed in the status bar. I understand reasonably well how Excel recalculates and the concept of the dependency tree and that there are now more than 65535 dependant cells.

It seems that having this "block of data" has dramatically increased the number of dependant cells and therefore a full recalculation is being done each time, which is not good. (point 3)

I could change all the links to individual cells again (there are lots!) but I like the principle of having IMPORT and EXPORT sheets.

Please can anyone offer some advice, guidance etc. on the points raised above.

Many thanks in anticipation.

Pete

 

[JazzSP8]

I'm no expert on this, and while I see your theory, I don't think that having one sheet to Import / Export data is going to work that well.

Especially if you're spreading yourself that much. When I read you had 65535 dependencies I winced a little bit for you. I've learnt from experience not to link Workbooks together for various reasons. If I do create a Workbook that needs data from another I'll have a Macro update it instead by opening the external workbook as read only and pulling in the data that way.

So much can go wrong with linked workbooks I feel.

One thing I'd look at if I wanted to continue the way you are are your formulas. There is a reason they're constantly trying to update themselves - Are you using Volatile functions for instance? > http://www.decisionmodels.com/calcsecretsi.htm <

Other thank that there is a fair few articles written on how to keep Excel happy when it comes to this kind of thing, one I just found from a quick Google was this one; https://www.oreilly.com/library/view/excel-hacks-2nd/9780596528348/ch01.html

As you'll see, one of the early tips in that article is that you shouldn't be spreading the data over Workbooks or even Sheets :-/

As I said, I'm not an expert but thought I'd throw my tuppence in anyway - Hope it helps