The Related Row Lookup Data Type

  • Updated

 The Related Row Lookup data type mceclip0.png is a data type for displaying values from related worksheets. Once a row has been related, you can use a related row lookup to display any values from the linked row. Further, combining Related row lookups and formulas allows you to maintain a central source for data. You need not replicate data in multiple places and you gain access to Spreadsheet.com's functions for manipulating data. 

Set up a Related row to link worksheets, and then you can lookup values from related rows. Use these values from Related row lookups as inputs in formulas, or lookup the output of a formula in another worksheet. Related Row lookups and formulas are especially useful for budgeting, analytics, customer metrics, and similar data, where you want to ensure consistent data but also be able to work with data in multiple worksheets.

Note: Related row lookups can only be applied at the column level, and only returns data for the rows that are already linked.

mceclip1.png

Topics in this article:

About Related Row Lookups

Once you have linked two sheets of data with a Related Row, the Related row lookup data type allows you to fill columns with data from related rows in the linked worksheet.

For instance, a workbook for a painting company, they have a worksheet for upcoming projects and another to estimate budgets. Projects from the upcoming projects worksheet have been related to rows in the budget worksheet, but now we need to set up related row lookups to pull relevant data (like hours estimated and paint colors to get paint costs). In the animation below, we can see that our related rows have already been set up but there are three empty columns we need to fill. We set each of these to related row lookups in the Edit data type menu, and select the column to lookup from.

Related_Row_Lookup.gif

Related row lookups ensure that you do not duplicate data in multiple places. Instead, data is kept in a single worksheet, related to other worksheets, and then you can look up that data to display it or use it in functions. If the data is changed in one sheet, this change is populated on all related sheets. 

Related row lookups are helpful when you want to reference particular columns in other worksheets, especially if you have already related both sheets. Such as relating a worksheet for user feedback to a worksheet for bug tracking, or a worksheet on job postings to another worksheet for interview tracking. 

Strict

By default, the Related row lookup data type has Strict enabled. Users cannot enter data into these cells, only populate them with lookups to related rows. To change the data that appears in a lookup, users must edit the data in the related worksheet. Either by expanding the related row, or navigating directly to the worksheet where the data originated.

Column to lookup from related worksheet

In the dialog box that appears when you change a column to a Related row lookup, you will see all available columns in the related row and can choose the one you want to relate to. For instance, in the animation above we select column D "Team Comments" to populate our matching column of the same name.

How to set up a Related row lookup

To set a column to the Related row lookup data type, your rows must first be linked to another worksheet via the Related row data type. See the linked article for more details on how to set up a related row.

Next, select the column you wish to turn into a Related row lookup, right-click on the column header, and select Edit data type. An Update Column menu will appear, select the Related row lookup data type from the drop-down of data types. The Column to lookup from related worksheet will then appear, displaying the name of the linked worksheet, and the columns you can choose to lookup. Select the desired column and click Update.

mceclip0.png

Spreadsheet.com will automatically update the values for that column with the corresponding value in the linked rows. Any rows without a linked row will remain blank until a row is related.

Related_Row_Lookup_When_empty.gif

Using Functions with Related Row Lookups

After setting up a column to lookup a value in a related row, you can use that the values from that lookup for any functions in your worksheet. For instance, in the animation below, we want to estimate the cost of the raw materials of our painting projects, so we create a formula to calculate materials costs by multiplying the number of gallons of paint by the cost per gallon plus fixed costs.

Formula_on_lookup.gif

We could also, if desired, add a function to the related worksheet and then lookup the output of that formula. In the animation below, we create an estimated number of gallons formula in our project worksheet, then go back to budgeting to see our related row lookup is populated by the output of that formula.

Formula_to_fill_lookup.gif

If you do not yet know how to link worksheets, check out our article on related rows. Or, for more on formulas, start with our creating and editing formulas article.