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
- Linking Rows Across Worksheets
- Expanding a Related Row
- Edit Rows and Add New Rows to your Related Worksheet
- Unlinking Rows
- Adding a Related Row Also Creates a Relationship in the Related Sheet
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.
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.
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 or a breadcrumb icon to see the related rows .
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.
In the animation below, we link multiple corresponding issues (from the Issues & Risks worksheet) to their corresponding tasks (from the Tasks worksheet).
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:
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.
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 add a row to a related worksheet, first, open the Select row menu by clicking on the link icon 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).
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 cell. 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.
Adding a Related Row Also Creates a Relationship in the Related Sheet
In addition to linking rows in the worksheet where you created the relationship, Spreadsheet.com creates a new column in the target worksheet that shows the linked rows from the original worksheet.
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.