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.
What are 1-way and 2-way Links?
When a Related row column is designated as 1-way, 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). With a 1-way link, information from the source worksheet is brought into the target worksheet without that relationship being reflected in the source worksheet itself.
When a 1-way link is established, changes in the source worksheet will be reflected in the target worksheet.
When a Related row column is designated as 2-way, 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.
When a 2-way link is established, changes made to the source worksheet will be reflected in the target worksheet and changes made to the target worksheet will be reflected in the source worksheet.
2-way links can only be created for column-level data types. Range and cell-level data only support 1-way links, as do self-referential relationships (those where the source and target worksheets are the same).
Let’s take a look at an example of each type of link.
Relating Rows with a 1-way Link
In the example below, we’ll relate two worksheets in the same workbook. Our target worksheet is a “Retail Locations” worksheet containing information about different retail stores, and our source worksheet is a “Regions” worksheet containing information about the regions that the stores are located in.
You might notice that Column B of our Retail Locations worksheet contains values that match Column A of our Regions worksheet. Right now, these are plain text values, but we want to turn them into Related rows.
For this example, we’ll establish the relationship as a 1-way link in the Update column dialog. Now, let’s compare our two worksheets again.
In the Retail Locations worksheet, the values in Column B have been changed to the Related row data type, but the Regions worksheet remains the same. The 1-way link is bringing data into our target worksheet from our source worksheet without reflecting the new relationship in our source worksheet.
The 1-way link allows us to create Related row lookups and Related row rollups in our target worksheet, but not our source worksheet.
Relating Rows with a 2-way Link
Let’s repeat the same process as above, this time establishing our Related row column as a 2-way link. We’ll begin with the same two worksheets as before.
Once again, we want to create a relationship between Column B in our target worksheet (Retail Locations) and Column A in our source worksheet (Regions).
This time, when we open the Update column dialog, we’ll select 2-way from the “Link” dropdown. Now, let’s compare our two worksheets again.
Not only has Column B in our target worksheet been converted to the Related row data type, but a new column, Column D – Retail Locations, has been created in our source worksheet. The new column is populated with the primary column values from our target worksheet. The 2-way link is bringing data from our source worksheet into our target worksheet and reflecting the relationship back into our source worksheet.
The 2-way link allows us to create Related row lookups and Related row rollups in both our target and source worksheets.
Changing the Direction of a Related Row's Link
Once a Related row has been designated as a 1-way or 2-way link, you can’t convert the relationship from one to another directly.
To change between link directions,
- Convert the Related row column in your target worksheet to the Automatic or Text data type
- Convert the column back to the Related row data type and select the intended link direction in the Update column dialog
Learn more about working with Related rows, including creating Related row lookups and Related row rollups. Or, read on to learn more about the different types of relationships that Related rows can reflect.