Creating Relationships with the Related Row Data Type

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.

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.

Source-and-Target.png

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.

Create-Related-Row.png

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.

Target-Worksheet-Related-Row.png

For example, clicking on “Math” in Cell D5 opens the expanded “Math” row from the source worksheet.

Expanded-Related-Row.png

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.

Related-Row-Dropdown-Options.png

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.

Create-New-Related-Row.png

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).

Source-and-Target-2.png

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.

View-Linked-Rows.png

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.

View-Linked-Rows-2.png

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…

Unlink-Row.png

…or simply delete the value from the Related row cell.

Read on to learn more about working with Related rows, including deep dives into 1-way and 2-way links and the types of relationships between data that can be reflected in Related rows. Or, learn about using your Related rows to create Related row lookups and Related row rollups.