The Related Row Data Type

  • Updated

 

In Spreadsheet.com, related rows link to rows in other worksheets and workbooks. Related rows work like connected tables in a relational database; editing one changes the data in another. With related rows, you can create relationships with rows in any worksheet you have access to, including worksheets in different workbooks and workbooks in different folders.

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

Related rows are a data type and, like other data types, you must have sufficient permissions to change the data type or data in cells. Cells, columns, and ranges of cells can all be set to the Related row data type or the Related row lookup data type. The Related row lookup depends on a row already having a related row and returns the value from one column in the related row. The related row data type actually sets up the relationship between a row in your worksheet to another worksheet.

Topics in this Article:

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 link our Project Notes worksheet to our Project Plan worksheet, which will allow us to tie a row of comments to the assigned task. 

mceclip0.png

In this dialog box, you will see all folders, workbooks, and worksheets 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.

Set_Related_Row_Data_type.gif

Linking Rows Across Worksheets

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 link rows mceclip1.png or a breadcrumb icon to see the related rows mceclip2.png.

mceclip0.png

To link rows, select the link icon and a dialog box will open that shows existing rows in the target worksheet that you can choose to link to.

mceclip1.png

In the animation below, we link multiple corresponding issues (from the Issues & Risks worksheet) to their corresponding tasks (from the Tasks worksheet).

Linked_Related_Rows.gif

Once you select a row or rows to link (you can link multiple rows), the rows will appear as clickable items in that cell, titled by the values they have in their primary column. For instance:

mceclip2.png

From the Related rows menu, selecting the + Add a link option also allows you to create a new link to a row in the target worksheet. New links are created beneath the existing linked rows.

Expanding a Related Row

Clicking on the linked row expands that row in the related workbook, 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.

Expanding_Related_Row.gif

Edit Rows and Add New Rows to your Related Worksheet

Once you have linked two worksheets, you can edit either worksheet from either worksheet. For instance, we can edit a risk in our Issues & Risks worksheet from inside our Project Plan's Tasks worksheet.

To edit an already related row, first expand the related row, and then make any desired edits directly in the expanded row view.

Editing_a_Related_Row.gif

To add a row to a related worksheet, first, open the Select row menu by clicking on the link icon mceclip1.png and then select + New row. This will open a new empty row that you can fill out, which will be added to the related worksheet.

In the animation below, we add a new issue (to the Issues & Risks worksheet of our Project Plan) and see that this is linked to the task we created the issue through (in the task worksheet).

New_Row.gif

Unlinking Rows

Rows that have been linked can be unlinked. To unlink a related row, first open the related row menu by clicking on the breadcrumb in the desired cellmceclip2.png. All linked rows will appear in this menu. To unlink them, hover over the related row and select the X in the upper top right corner.

mceclip4.png

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.

RelatedRow2Way.gif

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.

RelatedRow1Way.gif

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.

Screen_Shot_2021-10-06_at_2.17.04_PM.png

Related Row Lookup

The Related Row Lookup data type does not relate two worksheets together but allows you to look up individual values in an already linked worksheet. Once you have a related row setup, you can then set a cell or column to the related row lookup data type and choose the column to display in your lookup. 

For instance, in the animation below, we have already linked our Issues & Risks worksheet to our Tasks worksheet, including linking specific issues to specific tasks. In the animation, we create a new column, set it to the Related Row Lookup data type, and select a column to lookup. For rows with an existing link, this lookup inserts the corresponding lookup value. 

Related_Row_Lookup.gif

For more details on related rows, check out all our articles on Related Rows or our article on the Related Row Lookup data type for more on related row lookups.