Find Links to Other Spreadsheets

Nearly everyone who has worked with large document productions has had the worrisome experience of opening an Excel spreadsheet and getting this message:

. . . where are these missing files and how will they effect the data shown on the spreadsheet? Clicking on 'Edit Links' will show a list of the files that must be found.

Accounting for the production of the files that are linked to produced native Excel files is often an overlooked part of electronic discovery. If you can search through the metadata and find an Excel file whose filename matches that of the linked to file (and the metadata specifies the network folder it was collected from), it's still hard to be sure that the version of the file is same as the one used for the same version of the produced native file you need to review.

It is however always possible to find out which cells on the spreadsheet will be effected by the absence of the linked to file(s). We want to find where there are references such as:

=VLOOKUP(A37504,'C:\FooFolder\baseball\[fielding.xls]Fielding'!A$1:F$65536,6,FALSE)

In Find, enter '.xl', with 'Look in:' set to 'Formulas'. The results will list each cell with a link to an outside file. The search results are hyperlinked to the individual cells.

Merging Queries

In Excel you can easily merge two different workbooks which contain a common field.

1. On the Data tab, select New Query and load workbooks into Navigator.

2. Right click on the worksheet you want to import and select 'Load'.

3. On the Data tab, in the New Query menu select Combine Queries . . . Merge. A new dialog box will open, allowing you to select two of the loaded queries, and choose to load all rows from both, or just those from one which match the other. Highlight the matching fields from each source.

4. The Query Editor will open. You can add the columns from the second query by clicking on the NewColumn expand icon.

5. Click Close & Loan and the merged data will be added to a new worksheet.

Form Controls

Form Controls in Excel allow you to guide how users enter data in a worksheet. On the Developer tab, you'll see 9 options in the Insert menu.

The command button allows you to run a macro when the button is clicked.

A combo box will allow users to select options from a drop down list.

A check box can be used to enter either TRUE or FALSE in a designated cell depending on whether the box is checked or unchecked.

The spin button can be used to increase or decrease a value in a linked cell.

The minimum, maximum and incremental value can be set for the cell.

A list box lets a user choose from a list of items, and then assigns the number of the item to a designated cell.

Option buttons in a group box allow a user to select one of multiple choices to enter in a designed cell.

The label button simply adds text to describe a form control.

Finally a scroll bar allows you to scroll back and forth to change the value in a designated cell.

Right click on any of these form controls in order to resize or reset them.