Reporting on Information from Multiple Exports

Sometimes, you may need to produce a report that contains information from multiple exports (spreadsheets). Using Microsoft Excel’s LOOKUP functions (LOOKUP, VLOOKUP, HLOOKUP), you can perform the exports needed and “look up” information in a source (master) worksheet and display it in your destination worksheet.

Information can be linked via the appropriate “Data Link Fields” which uniquely identify the information in your exports (i.e. the unique ID for a volunteer profile, the unique ID for a specific hours log entry, etc.).

For example, you might want a report on all those assigned to a specific activity (i.e. the “Schedule Detail Export”) but also include the volunteer’s T-shirt size (i.e. a custom field value in the “Personal Profile Export”).

1. Perform a Volunteer Profile Export of all volunteers, ensuring that you include all volunteers, the desired profile information (e.g. the custom field for the volunteer’s T-shirt size) and the “DatabaseUserId” column.
2. Save the Volunteer Profile Export to a file. This will be your “source” worksheet.
3. Perform a Schedule Detail Export for your desired activities, ensuring that you include the “DatabaseUserId” column.
4. Save the Schedule Detail Export to a file. This will be your “main” worksheet where you will construct your report.

1. Open the Volunteer Profile Export.
2. Open the Schedule Detail Export.
3. In the Volunteer Detail Export, right click on the tab at the bottom containing the name of the worksheet and select “Move or Copy…” (shortcut: press the [ALT] key, then E, then M)
4. In the “Move or Copy” dialog:
   • In “To book”, select the name of the Schedule Detail Export file
   • In “Before sheet”, click on “(move to end)”
   • OPTIONAL: Check the “Create a copy” box
   • Click the [OK] button
5. Open the Schedule Detail Export. Your profile information should now be the second worksheet in that file.

[alert heading=”NOTE:” type=”alert-danger” block=”false” close=”false”]It is important to sort the source information (i.e. the values you want to look up) by a unique value. In this case, that is the “DatabaseUserId” column.[/alert]

1. Go to the profile information worksheet
2. Highlight the data
3. Sort the data by the DatabaseUserId column

In the schedule detail worksheet, you can now add a column to look up the T-shirt information for your volunteers.

1. Click in the blank cell in the first row where the information is to be copied
2. Enter the appropriate formula. The basic syntax for the LOOKUP function is: @LOOKUP(value_to_look_up,range_to_scan,range_to_return)
   • “Value to look up”: the single cell in your current worksheet containing the ID you want to find in your source worksheet
   • “Range to scan”: the range of cells in the source worksheet to check for the unique ID (this can only be one row or column)
   • “Range to return”: the range of cells in the source worksheet containing the data you want to return to your main worksheet (this can only be one row or column)
3. NOTE: Instead of entering the formula manually, you can follow the steps listed in the tip below
4. If you click in the cell where you created the formula, it might look something like this (where “Source” is the name of the worksheet that contains the data you are looking up):
   • =LOOKUP(A2,Source!A2:A101,Source!G2:G101)
5. Edit the formula to ensure that you are using an “absolute” cell reference instead of a “relative” one by adding a dollar sign (“$”) before each column letter and row number referenced in the source worksheet). This will ensure that the exact range is checked each time, rather than dropping the range down by one row in your source worksheet for each row of data in your master worksheet:
   • =LOOKUP(A2,Source!$A$2:$A$101,Source!$G$2:$G$101)
6. Copy the formula down to the last row of data in your main worksheet

[alert heading=”TIP:” type=”alert-info” block=”false” close=”false”]TIP: Instead of entering the formula manually, you can follow these steps:

1. Click in the blank cell (in the destination worksheet) in the first row where the information is to be copied and type @LOOKUP(
2. In the destination worksheet, select the cell value (typically in the same row) you want to use for your search. That cell address will be copied into your formula.
3. Add a comma (“,”) to the end of your formula (after the cell address you just copied)
4. Open the source (lookup) worksheet
5. Highlight all the data in the column you want to search (i.e. the data that matches what you highlighted in your destination worksheet), but don’t include the column header
6. Add a comma (“,”) to the end of your formula (after the range you just copied)
7. Highlight all the data in the column you want to return to your destination worksheet (don’t include the column header)
8. Type a right parentheses (“)”) at the end of your formula
9. Press the [Enter] key on your keyboard to copy the formula to the cell in the destination worksheet
10. In the destination worksheet, click in the cell where the formula was created

[/alert]

Here is a list of the “Data Link Fields” in the “Columns to Include” section of the exports.  Selecting a data link field (or multiple fields) enables you to “look up” matching information in another worksheet.

The following image illustrates the relationships between the Data Link Fields in the various export reports available.

For more information, please consult the Excel help pages on these functions:

• LOOKUP
• VLOOKUP
• HLOOKUP

You can also download these instructions as a PDF file.

Hours Reports

Feedback Field Reports

Schedules / Rosters

Volunteer Profile Exports

Note Log Export

[contentblock id=2]