Copy Literal

OAK's Copy Literal command copies cells from the one area of the active worksheet to another, without adjusting cell coordinates as Excel normally does.

When a cell containing a formula is copied to another location using the copy and paste commands, Excel goes to great trouble to adjust the cell references in the formula to reflect its new position.

For example if the cell A1 contains the formula =B3 and this cell is copied to B2, then the formula in cell B2 will be =C4, i.e. since the cell containing the formula has been copied one column across and one row down, the cell reference has been moved one column across and one row down. This behavior is generally exactly what is needed and is so automatic for spreadsheet users that they rarely think about the behaviour

However there are occasions when the adjustment is very much not wanted, and the wish is to make to copy the exact contents of a cell, so that in the example above the cell B2 would contain the formula =B3. This is what the Copy Literal function does.

•	When Excel copies formulas, it preserves them in R1C1 notation, which has the effect of causing them to change when shown in A1 notation. OAK's Copy literal command preserves them in A1 notation, which has the effect of causing them to change when shown in R1C1 notation.

•	It is like cutting cells and pasting them elsewhere, but without deleting the originals.

When checking a spreadsheet, Operis typically develops many reconciliations and tests. To avoid altering the original spreadsheet, it places the reconciliations and tests on a second spreadsheet. Operis refers to the first spreadsheet as the MUT (Model Under Test) and the second as the AWP (Audit Working Papers).

The second spreadsheet is full of formulas of the kind =WorkbookUnderTest[Sheet1]!A1, so that it can extract values from the first spreadsheet for examination. Such link formulas can be introduced

•	either, by hand: select a cell in the second spreadsheet, type an =, switch windows to the first spreadsheet, navigate to the top left corner of the region to be linked, press Enter, copy the formula as necessary

•	or, automatically: select the region in the first spreadsheet, copy it on to the clipboard, switch windows to the second spreadsheet, navigate to a suitable place in it, use Home \| Clipboard \| Paste \| Paste Link.

The second method is fractionally quicker and is often used by experienced analysts.

The spreadsheet being tested will typically have some hotspots that are the subject of several tests. If it generates financial statements, they are likely to be one such example. It may therefore be desirable to copy the link formulas from one test to begin another test.

Had the link formulas been hand-typed using the first method, the author might have anticipated the possibility of having to make further copies of them elsewhere in his worksheet, and added appropriate dollar signs to control the process. But the formulas inserted by Excel using the second, quicker method have relative references, unless there is just one cell involved. Any copy made by Excel's copy and paste (or equivalent methods) will point not at the hotspot originally selected, but at some cells that are offset from there.

4.	The exact contents of the source cell will be copied to the destination cell, prefixed accordingly if the destination is on a different worksheet.

OAK offers an alternative selection mode for the Copy Literal function, called Double-select mode. This mode can be enabled on the User Interface tab in the Options window. Once this has been enabled, do the following:

1.	Select the cell(s) you want to copy then press and hold Ctrl and click on the cell you want to copy to.

This command does not work when applied to protected worksheets. You must manually unprotect them first.

The destination cell should be a single cell. In double-select mode it must be blank, since otherwise it is not clear which cell is to be copied.

If the formula being copied acts on a range of several cells and identifies them by user-defined names, it is necessary to replace the names by coordinate references before copying them. OAK will perform this action. In fact, it will remove the names even when it isn't strictly necessary, for example when a single cell is identified by name.

If you copy a small number of cells and paste them into an area consisting of a larger number of cells, Excel's Paste command will repeat the source material as necessary to fill the target area. OAK's Copy Literal won't do this. It will take the target cell as its anchor, and place a single copy of the source cells there.

Competent Excel users will be able to think of several ways to circumvent the adjustment to coordinates that Excel performs when it copies formulas.

•	Adjust the cells by making cell references absolute; ie insert dollar signs as necessary.

•

Copy the literal contents of a cell. First select the cell you wish to copy, then press F2; this allows you to edit the contents of the cell. Now press Shift+Home to highlight the contents of the cell and press Ctrl+C to copy them. Then select the destination cell and press Ctrl+V. The literal contents of the source cell have now been copied to the destination cell. (But it's painful to do this for a range consisting of many cells.)

•

Highlight a range. Use Excel's Replace command to change the Equals signs to something else. (Before OAK had Copy Literal, Operis typically used &&& or ^^^, since they are unlikely to appear in a formula naturally) Since Excel expects formulas to start with equals signs, it will no longer consider that the cells contain formulas, and will copy them without coordinate adjustment. Excel's Replace command can then be used to restore the equals signs.