Search | Primary error cells

This command selects those error cells in the current selection that do not in turn reference another error cell in the current selection.  This command is most useful when the entire worksheet (or equivalently a single cell) is selected.

Why would you want to use it?

A single error value is usually propagated throughout a workbook because any cells that reference that error value (either directly or indirectly) will typically result in errors.  You will often need to examine a large number of error cells one at a time in order to identify the small number of cells that need to be modified.  This operation can be very time consuming.

The OAK4 | Search | Primary error cells command identifies those cells which contain the original error values and allows you to concentrate on fixing the source error cells rather than finding them.

How to use it

Select the range to be searched and choose the command from the menu; the command will either select those cells within the selection that contain primary errors or return a “No primary error cells found in selection” message.

Wrinkles

If you select a small number of cells on a worksheet and then select the OAK4 | Search | Primary error cells command, the resulting cells might not actually be primary error cells.  This is because they might refer to error cells outside your selection.  Therefore, in most cases, we recommend running this command on a whole worksheet.

However, you may find that even then, the cells which are found by the command do not constitute primary errors because they refer to error cells on a different worksheet in your workbook.  In this case you will have to run the command on that worksheet to find the true primary error cells.

Another option is to use OAK’s reconstructor to trace back the precedents of the cells found by the Primary Error Cell command. The reconstructor will lay out on a single worksheet a calculation equivalent to the one being examined.  The result could then be inspected by eye or one could use OAK’s primary error cell search on it.

The Primary Error Cells function might display a “No primary error cells found in selection” message when the selection clearly contains error cells.  This occurs when the workbook is circular and the primary error cells are on the circular path.  In this case, each error cell does reference another error cell.  This is just one reason why users should avoid building circular models.

What you could do if you didn't have OAK

Excel contains a function that is able to select error cells.  This function can be found under Edit | Go To | Special and then by selecting the formula and error options.  This function follows the one-cell rule (see page 17).  However, unlike OAK’s Primary Error command, Excel’s function will select all error cells within the current selection.  This means that the primary error cell must still be found by checking individual error cells.

Excel does however contain another mechanism for identifying primary error cells.   Excel's Tools | Formula auditing | Trace error command will draw red arrows on a worksheet to trace the source of an error.  The difference between this and OAK's Search | Primary error cells command is that it will only find one primary error, even if several are present; and selecting the error cells requires using the mouse to navigate the arrows drawn on the worksheet.