Related rows link rows in multiple worksheets so your spreadsheets can work like tables in a relational database – editing one changes the data in another.
The Related row data type is what’s used to create links between related data. Like all other data types in Spreadsheet.com, you can apply the Related row data type at the column, range, or cell level.
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.
This article features Spreadsheet.com's Course Scheduling template. Explore the Template Gallery to find ready-to-use templates in over 20 different categories, or learn more about starting a new workbook from a Spreadsheet.com template.
The Related Row Data Type
Related rows are created via the Related row data type and can be added to your worksheet like all other data types. First, select the column, range, or cell that you want to relate to data elsewhere. Right-click on your selection and select “Edit data type…” from the dropdown to open the Update column dialog (or Update data type, if a range or cell is selected) and select “Related row” from the Type dropdown.
To enable Related row lookups and Related row rollups, the Related row data type must be applied at the column level.
When selecting “Related row”, you will be prompted to select the worksheet to which you want to relate your data. Related rows can link to data in the same worksheet, data in a different worksheet, or data in a different workbook altogether.
The worksheet where the Related row is created is known as the target worksheet, and the worksheet to which it is related is known as the source worksheet. Related rows are linked to the values in the primary column of the target worksheet.
When you create a Related row, the related value is converted from text to a clickable link which expands the related row when clicked.
How to Create and Configure a Related Row
In the example below, we’ll use the Related row data type to create a relationship between two worksheets in a Course Scheduling workbook: Teachers, our target worksheet, and Subjects, our source worksheet.
We’ll create our Related row links in Column D of our target worksheet, which shares values with the primary column (Column A) of our source worksheet.
When you create a new Related row, you have a number of configuration options in the Update column or Update data type dialog:
Link
When creating a Related row column, you’re prompted with an option to designate the relationship as a 1-way or 2-way link. The type of relationship determines the direction in which information is shared across your related worksheets.
- 1-way Link: Data will only be populated in the target worksheet (the worksheet where the Related row column is created) and not in the source worksheet (the worksheet to which the target worksheet is related; the relationship is only reflected in the source worksheet.
- 2-way Link: Data will be populated in the target worksheet and a new column will be created in the source worksheet, populated with data from the target worksheet’s primary column; the relationship is reflected in both the source and target worksheets.
Think of Related rows as a conversation between your two worksheets. With a 1-way link, the source worksheet is talking and the target worksheet is listening. With a 2-way link, the source worksheet is talking and the target worksheet is talking back.
Learn more about directional links in our article “1-way and 2-way Links”.
Strict
The Strict option works as it does for all other data types – when strict is enabled, cells in your selection will be restricted to Related row entries only.
Allow linking to multiple rows
By toggling Allow linking to multiple rows, you can link a single cell of the Related row data type to multiple other selections in your source data. Linking to multiple rows is often used when the relationship reflected in your Related rows is a one-to-many or many-to-many relationship.
Related Worksheet
The Related Worksheet dropdown allows you to select the source data to which your Related row values will be linked. Whether your source data comes from the same worksheet, a different worksheet, or a different workbook, the Related rows in your target worksheet will always link to the source data’s primary column values.
Filter and sort selection by View
This dropdown allows you to restrict your Related row values to a specific View found in your target worksheet. Your source worksheet’s primary view is selected by default.
Once you’ve configured your Related rows as needed, click the blue Update column in the bottom left corner of the dialog to save your options and convert your selection to Related row values.
Expanding and Editing a Related Row
After applying your changes, the text values in your Related row column will be converted to clickable links that, when clicked, open the expanded view of the linked row in your source worksheet.
For example, clicking on “Math” in Cell D5 opens the expanded “Math” row from the source worksheet.
Changes made to these fields from your target worksheet will be reflected in the source worksheet.
Adding a Related Row
In the example above, we converted a column with existing values to the Related row data type, but you can also add new records to an existing Related row column. Click the downward arrow in the bottom right corner of a Related row cell to open a dropdown list of existing rows in the source worksheet.
You can also click the link icon in the bottom right corner of the dropdown to see more information about each row option. Or, click the blue + Add row button at the bottom of the dropdown to create a new row in your source worksheet.
Here, we’re creating a new “Advanced Math” row in our source worksheet without ever leaving our target worksheet. When we compare our source and target worksheets, we can see that a Related row link has been established in our target worksheet (Cell D5) and a new row has been added to our source worksheet (Row 14).
Viewing a Cell's Related Rows
By clicking on the downward arrow in the bottom right corner of a Related row cell, you can also view details of all of the rows to which a cell is related. This can be especially useful when a cell is related to multiple rows, like Column B of our Subjects worksheet where each cell is related to multiple rows in our Classes worksheet.
Double click on the Related row cell or click the downward arrow in the bottom right corner of the cell and click the View linked rows button in the bottom right corner of the dropdown.
This will open the Related rows dialog where you can view details about each of the Related rows in the source worksheet, as well as add additional rows to the source worksheet.
Unlinking a Related Row
To unlink a Related row, either hover over the row in the Related rows dialog and click the x icon in the upper right corner…
…or simply delete the value from the Related row cell.
Learn more about working with Related rows in our series of articles on relationships between worksheets. Or, read on to learn more about the rest of Spreadsheet.com's Data Types including Related row lookups and Related row rollups.