Leading the way in Microsoft Office Development

Auditing Complicated Worksheets

There may be times when you need to modify (or check) the formulas used in a workbook (or worksheet).

This job can be quite tedious at the best of times but to make it a bit easier it is possible to display both the formulas and the results at the same time.

To achieve this you need to open a separate window on the same workbook.

Start by opening up the workbook that contains your formulas.

Select (Window > New Window).

Select (Window > Arrange) and select Vertical.

In the left hand window press (Ctrl + ' ) or alternatively select (Tools > Options)(View tab, "Formulas").

Circular References

Excel does not repeat the "cannot resolve the circular reference" error message if it fails to resolve the reference.

When linking cells between different worksheets and different workbooks although try and have a ?? as opposed to random linking.

Also be aware that if cells are linked / references and the original cell is deleted then #REF appears in all the original formulas that referenced that now “deleted” cell.

When copying links to an external market data source between spreadsheets with the same name you must rename one of the workbooks and open them both up together.

It will not work by copying then closing and opening the new one.