Quick Start: Related Rows and Lookups

  • Updated

Related rows link rows in multiple worksheets by acting 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

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, cell, or range of cells, right-clicking, and selecting "Edit data type..." from the dropdown. Or, click the downward arrow to the right of the column name and select "Edit data type..." from the dropdown.

Edit-Data-Type.jpg

This will open the Update column dialog, where you can select "Related row" from the Type field at top. The Update column dialog allows you to configure your Related row data, beginning with selecting the Related Worksheet.

Update-Column.jpg

Your related worksheet can be another worksheet in the same workbook, or a worksheet in a different workbook altogether. The Update column dialog also allows you to restrict data to only Related rows via the "Strict" toggle, allow linking to multiple rows, and specify whether the relationship is 1-way or 2-way, which we'll explore further later in this article.

In this example, a Course Scheduling workbook, we're creating a new column – "Subject" – in the "Teachers" worksheet and relating it to the "Subjects" worksheet.

Once you have chosen the worksheet to relate to, you can link specific rows together. Clicking the downward arrow in the bottom corner of a Related row cell opens a dropdown menu with the list of possible rows to relate to, listed by their primary column values.

Relate-Row.jpg

You can select the row to relate from one of these options, or click the Link a row icon mceclip1.png in the bottom right corner to open the Select row dialog, where you can see more information about each row. Alternatively, click the + Add row button in the bottom left corner to add a new row to the linked worksheet.

If you begin typing the name of a row in your Related row column, Spreadsheet.com will autosuggest matches.

Once you select a row option in the Related row column, you will have a linked record between the two worksheets.

Adding Related Data to a Row (Related Row Lookup)

Once you've established a relationship between two worksheets, 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 Update column dialog.

Update-Column-2.jpg

Here, we're creating a new column – "Location" that will automatically lookup the location of each subject from the "Subjects" worksheet we previously related. The two screenshots below show the "Teachers" worksheet where we just created our new lookup column and the "Subjects" worksheet where our lookup is drawing the location data from.

Teachers-Lookup.jpg

Locations-Lookup.jpg

How to Edit Related Data (Expanding Rows)

Clicking on the name of 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.

Below, we've clicked on the Related row cell "Math" in Cell D5 of the "Teachers" worksheet to open a dialog showing the Related row "Math" in Row 8 of the "Subjects" worksheet.

Row-Dialog.jpg

You can edit the row from this dialog, or you can edit the row directly in its original location.

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.

In the next part of our Quick Start series, learn about working with indented rows and row hierarchies ➡️.