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.
1-way and 2-way Relationships
When setting up your related row column, relationships between related rows can be designated as 1-way or 2-way:
- 1-way relationships: When a related row column is designated as 1-way, a new column will not be created in the target worksheet. With a 1-way relationship, you are able to reference data in your target worksheet without having the relationship reflected in or modified by the target worksheet.
- 2-way relationships: When a related row column is designated as 2-way, a new column will be created in the target worksheet with information from the relationship in the newly designated related row column. This relationship will be reflected in – and can be modified by – the target worksheet.
Note that 2-way relationships can only be created for column-level data types. Range or cell-level data only support 1-way relationships, as do self-referential relationships (those where the source and target worksheet are the same).
Once a relationship has been designated as 1-way or 2-way, you cannot convert the relationship from one to another directly. To change the direction of the relationship, convert the related row column to Text or Automatic, and then convert it back to Related row
Let’s take a closer look. In the first animation below, we’ll explore 2-way relationships. Here, we’re converting a column of employee names in the “Locations” worksheet into a Related row column that references the “Employees” worksheet. Currently, the “Employees” worksheet has no column for location information. When we convert the column of employee names to a related row data type and designate it as a 2-way relationship, the workbook generates a new column – “City” – in the target worksheet.
Similarly, we can see what happens when we designate the related row as a 1-way relationship. We follow the same steps – converting the column of employee names into a related row data type – but designate the relationship as 1-way. Unlike the 2-way example, when we visit the target worksheet here, we see that no new “City” column has been created.
Filter and Sort Related Row Selectors by View
The Related row dialog also allows you to Filter and sort the resulting data by views and reference data from only a subset of rows within the target worksheet. To enable this, create a new view within the target worksheet. Then, when setting up your Related row column, select the new view from Filter and sort selection by View at the bottom of the Related row dialog. Your Related row column will only populate with results that appear in your selected view.