In Spreadsheet.com, values in Related row columns can be assigned with formulas just like any other text strings.
Many of Spreadsheet.com's unique data types like the Related row data type can be assigned via formulas. Combining formulas with the Related row data type allows you to create complex logic to determine which related values to assign, or even have your worksheets change based on changes to data in other worksheets.
How to Input Formulas in Related Row Cells
You can create formulas in Related row cells the same way you would input a formula anywhere else – by typing it directly in the cell or by using the formula bar above the worksheet header.
For a formula to output a corresponding Related row value, your formula must reference an existing Related row cell as an output and not the explicit text string that matches the Related row value.
In the example below, we've created a reference table in the header region that links a room type with a corresponding paint color, expressed as a Related row linked to a separate "Paint Colors" sheet. Then, in the table region, we use VLOOKUPs to assign a paint color to each room based on the room's type.
Note how we're using a cell reference in our VLOOKUP formula instead of calling the text string directly. If, for example, we were to set the output of Cell B7 to explicitly reference "Atmospheric Blue" instead of referencing Cell C3, the Related row relationship would not be created despite Column C being set as the Related row data type.
To learn more about other data types that can use formulas to assign values, check out the rest of our Advanced formatting and assigning values with formulas articles. Or, take a look at our articles on creating relationships between worksheets to learn more about Related rows, Related row lookups, and Related row rollups.