Before creating Related rows in your workbooks, it’s important to understand the different types of relationships that can exist between your data and how they can be reflected in Related rows, Related row lookups, and Related row rollups.
To understand how these relationships are reflected in Spreadsheet.com, it can be helpful to visualize these relationships diagramatically. Below, we’ll look at how three different types of relationships are represented both visually and with Related rows.
One-to-one relationships are the simplest type of relationship, where each item or record has one other item or record to which it is related. One-to-one relationships reflect unique pairs of data, like a sale and its invoice number, a student and their student ID number, or an airport and its airport code.
In these relationships, each item can only be matched to its counterpart. For example, San Francisco International Airport can only have the code SFO, and the code SFO can only be used to identify San Francisco International Airport.
One-to-one relationships are commonly encountered in sales workbooks with invoice tracking and personnel workbooks with identifying information about individuals, among other applications.
When expressed with Related rows, one-to-one relationships might be related within the same sheet, across different worksheets, or across different workbooks altogether.
Creating One-to-One Relationships with Related Rows
In the example below, we have a simple worksheet showing a list of Students in Column A and their project partners in Column B. This relationship is one-to-one, as each student can only have one partner.
In Column B, we’re using the Related row data type to create a relationship between it and Column A in the same worksheet. We can also use Related rows to reflect one-to-one relationships across different worksheets.
One-to-many relationships are more complex than one-to-one relationships, as each item or record can be related to multiple other items or records. One-to-many relationships are seen in many different applications, like a region and the different retail stores within it, a manager and the multiple employees they are responsible for, or a portfolio and the different stocks it is made up of.
Note that on the “many” side of the relationship, each item only has one counterpart. One region can contain multiple stores, but each store can only belong to one region.
When expressed with Related rows, one-to-many relationships are often related across worksheets or workbooks, and can be visualized with 1-way or 2-way links.
Creating One-to-Many Relationships with Related Rows
In the example below, we have a retail sales workbook with two worksheets – Retail Locations, our target worksheet, and Regions, our source worksheet. In our Retail Locations worksheet, each location has an associated region listed in Column B.
In Column B of our Retail Locations worksheet, we’re using the Related row data type to relate each location with the region listed in Column A of our Regions worksheet. The column has been created with a 2-way link so that the relationship is reflected in both the target and source worksheets.
The 2-way link helps visualize the one-to-many relationship being expressed with our Related rows. Column B in our Retail Locations worksheet shows only one region per store, while Column D in our Regions worksheet shows many stores per region.
In our Regions worksheet, for example, we could add a Related row rollup column showing the total revenue for all stores in a region, or the earliest year that a store was established in a region. Or, in our Retail Locations worksheet, we could add a Related row lookup column showing the regional director associated with each store.
Many-to-many relationships are even more complex than one-to-many relationships, as multiple items or records can be related to multiple other items or records. For example, one person can be working on multiple projects and each project can in turn have multiple people working on it, or a student may be enrolled in multiple courses, each of which has multiple students enrolled in it.
Many-to-many relationships are common across all sorts of applications, from organizational structures to financial portfolios to employee directories and more.
Like one-to-one and one-to-many relationships, many-to-many relationships can also be expressed with Related rows. These connections are often linked across workbooks or worksheets with 1-way or 2-way links, and may require the use of junction tables for more complicated applications.
Creating Many-to-Many Relationships with Related Rows
In the example below, we have a client staffing workbook with two worksheets – Consultants, our target worksheet, and Clients, our source worksheet. In our Consultants worksheet, each consultant has a list of clients they’re staffed to in Column D.
In Column D of our Clients worksheet, we’re using the Related row data type to relate each consultant with their clients, listed in Column A of the Clients worksheet. The column has been created with a 2-way link so that the relationship is reflected in both the target and source worksheets.
When using Related rows to create many-to-many relationships, make sure that “Allow linking to multiple rows” is enabled when setting up your Related row column.
The 2-way link helps visualize the many-to-many relationships being expressed with our Related rows. Each consultant is assigned to multiple clients, and each client in turn has multiple consultants assigned to them.
Creating Junction Tables with Related Rows
Sometimes, expressing many-to-many relationships requires more than just the knowledge that there is a relationship between two items or records. Many-to-many relationships often involve information about the nature of the relationship as well.
For example, in the staffing example above, our Related rows only tell us which consultants are staffed to which clients, but don’t tell us anything about the role that each consultant plays with each client they’re staffed to. We may want to know something like how heavily involved each consultant is with each project so that we can better visualize workloads or establish points of contact. Or, we may want to know how much each client is being billed per week.
Instead of trying to manually add these details to one of our two existing sheets, we can create a third sheet, known as a junction table, to collect and aggregate information about the relationships between our two existing sheets.
To this point, the relationship between our Consultants and Clients sheets has worked like this:
Our Consultants sheet feeds information to our Clients sheet, which in turn feeds information back into our Consultants sheet. When relating data, this limits us to the information that’s kept in either sheet. By introducing a junction table to our workbook, we can enable a more complex flow of information that takes into account information held in a third worksheet, like this:
If we introduce a third Staffing Details worksheet, we can create a more robust data feedback loop. The Staffing Details sheet can take in information from our Consultants and Clients sheet, merge that information with its own data, and then return new, useful information back to our existing sheets.
Let’s take a look at creating a junction table in our workbook.
Above, we’ve created a new worksheet – Staffing Details – with one row for each consultant-client pairing and added some new information in Columns C (Role) and Column E (Est. Weekly Hrs.). Column D, our primary column, uses the CONCATENATE formula to combine the information in Columns B and C. This worksheet will serve as our junction table.
The data in Columns A and B is text for now, but we can change both columns to the Related row data type and relate their information with our Consultants and Clients worksheets, respectively.
Because we’re creating a 2-way link for each column, information from our junction table is now visible in our original Consultants and Clients worksheets.
Now, in Column E, our Consultants sheet includes information from the Staffing Details worksheet’s primary column for each consultant. Let’s go back to our junction table for now and add additional information.
By using a Related row lookup, we can bring the hourly billing information from Column C of our Consultants sheet into our junction table.
With the Related row lookup in Column F and the estimated weekly hours worked per project in Column E, we can calculate the weekly amount billed per consultant per project in Column G by multiplying the two values.
Next, we can aggregate that information for each client and bring it into our Clients worksheet with a Related row rollup.
Thanks to our junction table, our Clients sheet can now show a simple accounting of the amount billed weekly for each client.
Let’s add a similar Related row rollup to our Consultants sheet to see the total number of hours each consultant works per week to better understand their workloads.
Finally, we can use another Related row rollup in Column G to see what amount each consultant is billing for each week. As a last step, we’ll hide columns throughout the workbook that now contain extraneous information.
When we compare our original workbook…
…to our new workbook…
…the impact of our junction table is evident.
Instead of only showing that a relationship between our Consultants and Clients sheets exists, our workbook now reveals important information about the relationship, including more useful staffing details and aggregated information like billing totals and workload data.
By using Related rows with 2-way links, Related row lookups, and Related row rollups in conjunction with our junction table, we can bring this information into our primary worksheets without unnecessary manual calculations, additional data entry, or clutter from intermediate information.