In Spreadsheet.com, related rows allow you to link to rows in other worksheets. They work like tables in a relational database: editing one changes the data in another. You can create relationships with rows in any workbook you have access to, even workbooks in different folders.
Related rows are a great way to connect data across multiple workbooks. For instance, if you have one worksheet to track job candidates, another to track job descriptions, and a third to manage interview scheduling, you can connect all three and make edits to data in one that will be reflected in the others.
- How to Relate Worksheets
- Adding Related Data (Related Row Lookups)
- How to Edit Related Data (Expanding Rows)
- Adding a Related Row Also Creates a Relationship in the Related Sheet
How to Relate Worksheets
To set up a relation between one worksheet and another, first select the column, cell, or cells in your existing worksheet that you would like to relate, and change their data type to the Related Row Data Type. You can do this by selecting a column, right-clicking, selecting Edit data type..., then Related Row from the data type selector. From there, you can choose to restrict data to only related rows, allow linking to multiple rows (checked by default), and choose which worksheet to relate to.
Once you have chosen the worksheet to relate to, you can link specific rows together. In each related row cell, you should see the option to select row to relate to or to add a link to this row via related the related row options button .
When you select the plus sign to relate a row, a dialog box opens showing existing rows in the target worksheet that you can choose. Once you select a row or rows to link, the rows will appear as clickable items in that cell, populated by the data in their primary column. For instance:
Putting this all together, in the animation below, we are in our Applicant Tracker and add a new column to relate the Interview Tracker worksheet. We add a new column, edit the data type to Related Row, select the Interview Tracker worksheet in the Interview Tracker Workbook, and choose the row to relate (in our example, the relationship is based on the last name).
Adding Related Data to a Row (Related Row Lookup)
Once you have related worksheets together, you can populate data from the other related worksheet with the Related Row Lookup data type. To lookup data, create a new column or select the existing cell, range of cells, or column you would like to populate and change its data type to Related Row Lookup .
From the edit data type dialog box, you can select the column from the related worksheet that you would like to lookup.
After you have selected the column to lookup and clicked Update, your column, cell, or range of cells will populate with the lookup data. For instance, in the animation below, we are in our Applicant Tracker Worksheet, set our interview date column to the Related row lookup data type, click update, and see data from related rows populate.
How to Edit Related Data (Expanding Rows)
Clicking on the linked row expands that row, opening a dialog box that lets you see and edit all the data contained in the linked row. Changing data in this dialog box will also change the data in the linked worksheet.
For instance, in the animation below, we click on the "Baker" related row to expand the row from another worksheet. From there we can see all the data entered into that row and even make comments and @mentions that will appear in both worksheets.
Adding a Related Row Column Also Creates a Related Row Column in the Target Worksheet
In addition to linking rows where you created the relationship, Spreadsheet.com creates a new column in the target worksheet that shows the linked rows from the original worksheet. Instead of linking data from another worksheet into the worksheet you are currently in, you can link data from your current worksheet into another. By clicking the related row options button, you can choose to add a link in the target worksheet:
Also, in the Select Row dialog box, you can choose + New row (). This allows you to create a new row in the related sheet to then reference here. New rows are created beneath existing rows.