Turning off data tables via formula

All -

I have a (necessarily) complex spreadsheet with some associated data tables for running senstivities. The need to run these sensitivities is only occasional. The spreadsheet is being used by a range of people in different organisations.

'Automatic recalculation except datatables' works fine to keep calc times reasonable. However, the other users are reporting instability when they save. Obviously Excel recalcs the datatables on save, and I think if the user has other spreadsheets open, these may all get recalculated too. I'm not sure if this is actually causing instability, but I can certainly believe the long calc time is giving the perception of a freeze.

There are various options I can think of, but they all have weaknesses:

1. Teach the various users to hit 'escape' to stop the recalc-on-save (though my own experience is that this can genuinely lead to a crash)
2. Switch to pure manual recalculation, with no recalc on save (though this is error prone for the user, and anyway just means that they have to take the 'full recalc' hit in the middle of their work, rather than on saving)
3. Apply some VBA (though I'm not sure the various organisations where the users are will allow them to run VBA)

So I was wondering whether there was a way to temporarily 'neutralise' the datatables via formula. My thought was to set up the datatable output cells along the lines of

=IF(data_tables_live, interesting_result, NA())

The idea was that if cell data_table_live was set to FALSE, then the cell feeding the datatable would not depend on the rest of the spreadsheet, and recalc would be fast. However, some experimentation suggests this doesn't work. Either Excel recalcs the entire spreadsheet by default for each cell in a datatable, or the dependency tree doesn't take advantage of the branching of the IF statement into a deadend in this case.

Is there another way to have a 'switch' for the datatables within the spreadsheet that will isolate them from recalc in a similar manner? Or indeed some other better solution I haven't thought of?

Thanks very much for your help

Rob

Trevor -

Thanks for coming back to me so quickly.

IIRC, datatables can only drive cells within their own worksheet. So the datatables in sensitivy.xls would have to refer to assumptions within that file. Thus model.xls would need to be driven off those same assumptions in sensitivity.xls. Thus the user would need to have both files open (and linked for the purposes of calculation) even if they were just doing basic amendments to inputs and not interested in running the tables. By extension, when they closed the file(s) they'd have the same recalc issues.

I think that puts me back where I started - but perhaps I'm missing something?

Thanks again
Rob

Having spent some more time with your idea of splitting the workbooks, I think it is the basis of a solution.

The assumptions and datatables can both sit in Sensitivity.xls. Model.xls can then link to those assumptions, but with a local copy also. I then build a data_tables_live switch into Model.xls. If 'No' then Model will work from its local copies of the assumptions. If 'Yes', then it will work off those drawn from Sensitivity.xls.

Having checked this, if only Model.xls is open, in does simple recalcs only. (It may or may not be pulling the assumption figures from the closed Sensitivity.xls file, but even if it is, this isn't triggering a datatable recalc). Users can use Model.xls on a standalone basis, changing input assumptions if they wish, getting expected results.

When it's time to run sensitivities, we open both files and set data_tables_live to 'Yes'. The datatables in Sensitivities.xls then do their thing, pulling results across from Models.xls as required.

So thanks for getting me to the answer!