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.
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.
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, as well as the Related row lookup and Related row rollup data types. Lookups and rollups depend on a row already having a related row, while the Related row data type sets up the relationship between a row in your worksheet and a row in another worksheet.
Relationships Between Worksheets
Often, data kept in one worksheet has a relationship with data in another. Sometimes these relationships are simple, like a worksheet with employee names that relates to a separate worksheet with employee numbers. Other times these relationships are more complex and may involve multiple worksheets, like a worksheet with retail locations that relates to another worksheet with retail location regions, which in turn relates to a third worksheet with regional personnel.
Related rows create links between shared data in related worksheets, bringing data directly from one worksheet into another without the need for lookup functions or data re-entry.
Let’s say we have two worksheets – a Student Roster and a Gradebook – that show different information, but both contain columns with the same list of student names.
Right now, these worksheets exist separately, but with Related rows we can make them “talk” to each other. If we relate these worksheets based on their common values – the student names – we can bring information like the student’s overall grade from our Gradebook into our Student Roster.
The data that’s brought into our Student Roster isn’t a new record, but a related record. If we make changes to the related data in our Gradebook worksheet, the corresponding record in our Student Roster will automatically update to reflect the changes.
Understanding the Relationships in Your Data
Relationships between worksheets take on many forms and range from simple to very complex. Understanding these different types of relationships is important when creating Related rows or using Related row lookups and Related row rollups. The three main types of relationships are:
- One-to-One Relationships: Each item or record has one other item or record to which it is related in a unique pairing. (Ex. a person and an ID number)
- One-to-Many Relationships: Each item or record can be related to multiple other items or records. (Ex. a list of regions and the stores in each one.
- Many-to-Many Relationships: Multiple items or records can be related to multiple other items or records. (Ex. a list of team members and the projects each one is working on)
For a deeper dive into identifying and understanding these relationships in your data, see our article on Types of Relationships.
Introduction to the Related Row Data Type
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.
To enable Related row lookups and Related row rollups, the Related row data type must be applied at the column level.
To set up Related rows, select the column, range, or cell that you would like to relate and change its data type to Related row. Then, you can select the worksheet to which you want to relate your data and specify whether or not you want to enable linking to multiple rows. You can even relate worksheets to worksheets in different workbooks, or relate a worksheet to a specific filtered or sorted view in another worksheet.
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.
In the example above, Column B in our target worksheet (Employees) is a Related row that is related to the primary column (Column A) in our source worksheet (Regions).
We’ll take a closer look at creating Related rows in our next article “Creating Relationships with the Related Row Data Type”.
1-way and 2-way Links
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”.
How to Expand and Edit a Related Row
Clicking on a Related row value expands the row, displaying the row information from the source worksheet as well as information about other rows in the target worksheet that are related to the row.
We’ll take a closer look at expanding and editing Related rows in our next article “Creating Relationships with the Related Row Data Type”.
Introduction to Related Row Lookups and Rollups
Related row lookups and Related row rollups are data types that can be used in conjunction with Related rows. To use these data types, the Related row data type must be applied at the column level.
The Related Row Lookup Data Type
The Related row lookup data type looks for the value of a cell in an existing Related row, similar to a lookup function like VLOOKUP. Related row lookups bring data from source worksheets into target worksheets and help ensure that you are not duplicating data in multiple places. Related rows and Related row lookups allow you to maintain a single source of truth for each record and automatically bring that data into linked worksheets.
In the example above, Column D in our target worksheet (Employees) is a Related row lookup that works with the Related row data in Column C to pull in values from Column B in our source worksheet (Regions). And Column D in our source worksheet (Regions) is a Related row lookup that works with the Related row data in Column C to pull in values from Column B in our target worksheet (Employees).
Learn more about Related row lookups in our article “Related Row Lookups”.
The Related Row Rollup Data Type
The Related row rollup data type allows you to perform calculations across linked rows in other worksheets and return aggregate data. Unlike Related row lookups, which return raw data from the Related row, Related row rollups can return summaries, or aggregates, of the related data. Related row rollups make it easy to quickly calculate aggregated values of data across multiple worksheets.
In the example above, Column C in our target worksheet (Regions) is a Related row rollup that calculates a sum of the revenue from the Related row values in Column B. The Related row was created in Column B of our source worksheet (Stores) with a 2-way link, so the relationship is reflected in both worksheets.
Related row rollups can also return other types of aggregate calculations like sums, averages, minimum and maximum values, and more. Learn more about Related row rollups in our article “Related Row Rollups”.
Read on to learn more about working with Related rows, including creating relationships between worksheets and using the Related row lookup and Related row rollup data types.