My firm has a lot of complex actuarial models in Excel which are distributed firmwide, and in order to discourage tinkering with them the workbooks are generally protected, other than cells which are intended for user inputs. The problem is that protecting workbooks automatically disables formula auditing (no idea why Microsoft did this). So if your job is assessing what the implications are of changing this or that input, or figuring out why the numbers moved the way they did and whether this is legit, this can make things a lot more difficult.
Question is whether there is a way around this. Two possibilities. Either 1) enabling formula auditing in protected worksheets, or 2) doing the initial protection in a manner which does not allow for modifying the protected cells but not using full protection.
(I believe an Excel workbook can be saved without sheet protection but not allowing modifications; this would still allow for formula auditing. But I believe this would make the entire workbook impossible to modify, while the intention here is to allow for medication in the user input cells.)
Any Great Ideas?