Assigning Users with Formulas

  • Updated

Many of Spreadsheet.com's unique data types (including the User data type) can be assigned with formulas. Combining formulas with the user data type, allows you to create complex logic to assign user, possibly referencing other cells, or even have your worksheets change user automatically based on results from other worksheets.

How to input formulas in user cells

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

mceclip0.png

Note: Your formulas must reference an existing user cell as an output to return a user data type. For instance, "=F11", rather than ="Aaron". A formula with an output of "Aaron" will simply return "Aaron" as text, not the user Aaron.

For example

For example, in our painting company's project tracker, we might have two painters who we want to divide work evenly between. But, instead of just alternating projects, we want to account for differences in scope. If one project takes 10 hours and another 1 hour, then it may not be fair to simply alternate. In the animation below, we have already added a formula that compares estimated hours between painters. We apply this formula one cell down, to assign the painter who has had the fewest hours.

Users_with_Formulas.gif

The formula in this animation looks at past projects and compares the total number of hours Aaron has worked to the total number Sara has worked. If Aaron has worked fewer hours, then it assigns this next project to him. If Lisa has worked fewer hours, then it assigns her. We even test the formula by changing the hours on past projects to show that increasing these hours changes who gets assigned (to ensure balance).

To see other data types that can use formulas to assign values, check out our advanced formatting articles. Or see our Collaboration & Sharing articles for more on the user data type and collaborating with other users.