Step 1: Run the Raw Data Reports
- Run the Personal Profile report, 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.
- Save the Personal Profile raw data to a file. This will be your “source” worksheet.
- Run the Schedule Detail report for your desired activities, ensuring that you include the “DatabaseUserId” column.
- Save the Schedule Detail raw data to a file. This will be your “main/destination” worksheet where you will construct your report.
Step 2: Combine Your Raw Data Reports Into One File
- Open the Personal Profile raw data report.
- Open the Schedule Detail raw data report.
- In the Personal Profile report, right click on the tab at the bottom containing the name of the worksheet and select “Move or Copy…” (PC shortcut: press the [ALT] key, then E, then M)
- 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
- Open the Schedule Detail report. Your profile information should now be the second worksheet in that file.
Step 3: Prepare Your Source Data
- Go to the profile information worksheet
- Highlight the data
- Sort the data by the DatabaseUserId column
Step 4: Look up Desired Information
In the schedule detail worksheet, you can now add a column to look up the T-shirt information for your volunteers.
- Click in the blank cell in the first row where the information is to be copied
- 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)
- NOTE: Instead of entering the formula manually, you can follow the steps listed in the tip below
- 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)
- 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)
- Copy the formula down to the last row of data in your main worksheet