Assigning Related Rows with Formulas

  • Updated

Many of Spreadsheet.com's unique data types (including 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 rows to link, possibly referencing other cells, or even have your worksheets change related rows automatically based on changes to data in other worksheets.

How to input formulas in related row cells

Like other cells, you can input a formula into related row cells by typing the formula. Simply select the related row cell, click on the function bar, and type in the desired formula.

mceclip0.png

Note: Your formulas must reference an existing related row cell as an output to return a related row data type. For instance, "=B10", rather than ="Clary Sage". A formula with an output of "Clary Sage" will simply return "Clary Sage" as text, not the Related row Clary Sage.

For example

For example, in our painting company's project tracker, we might have a project where the customer simply wants the cheapest paint we have available. In the animation below, we go into a related row cell, add in a formula, and then determine our cheapest paint color based on the output of the formula.

Related_Row_with_Formula.gif

The formula we paste in looks for the minimum paint cost in our paint colors worksheet, returns the name of that paint color, and we output that name as a related row. 

To see other data types that can use formulas to assign values, check out our advanced formatting articles. Or see our articles on Related Rows for more on the related rows data type and linking worksheets.