In Spreadsheet.com, Related rows allow you to link to rows in other worksheets. They work like tables in a relational database, editing in one changes the data in another. You can create relationships with rows in any workbook you have access to, even workbooks in different workspaces.
Related rows are a great way to connect data across multiple workbooks. For instance, if you are building new software and are tracking bugs in one worksheet and tracking your team's scrums in another worksheet, you can relate the relevant bugs to the scrum where they will be solved. Or, if you are developing a new commercial for your company's latest product, you can connect a worksheet of notes to another worksheet tracking your team's tasks. Once linked, you can even edit linked rows from your worksheet.
Setting a Column or Cell to the Related Row Data Type
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 Related Row . 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. For instance, below we choose to link our Interview Tracking worksheet to our Job Candidates worksheet, which will allow us to tie a row of upcoming interviews to the relevant candidate.
In this dialog box, you will see all Workspaces and Folders you have access to and can select a specific Worksheet to relate to.
Note: Even if you only select one cell to be a Related Row data type, you are actually relating the entire row to a row on another worksheet.
Selecting the Row to Relate
In the "Related worksheet" menu of the Edit data type dialog, you are able to select any worksheet in any workbook that you have access to. Once you have set a column to the Related row data type, choose the worksheet with the data you want to connect, row by row, to your current worksheet. For instance, in the animation below, we relate the "Interview Tracker" worksheet in our "Interview Tracker" workbook to the Applicant Tracking worksheet we are working in.
If you have trouble finding the exact worksheet, the Related worksheet menu has a search feature to help you locate worksheets.
Setting up the Related Row
Once you have chosen the worksheet to relate to, you can link specific rows together. In each related row cell, you should see icons to select the row to relate to or a breadcrumb icon to see the related rows and add a link to a row .
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 to related to.
Once you select a row or rows to link, the rows will appear as clickable items in that cell, with the value they have in their primary column. For instance:
From the Related rows menu, selecting Add link to a row () also allows you to create a new row relationship in the target worksheet. New links are created beneath the existing linked rows.
Below we show all these steps in sequence: 1) setting a column to the Related row data type, 2) selecting the worksheet to relate, 3) and picking a specific row to relate.
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.
Adding a Related Row Also Creates a Relationship in the Related Sheet
In addition to linking rows where you created the Related Row relationship, Spreadsheet.com creates a new column in the target worksheet that links back. Changing the linked data, in either place, will ensure data is changed in both places.