Assigning Users with Formulas

In, values in User columns can be assigned with formulas just like any other text strings.

Many of's unique data types like the User data type can be assigned with formulas. Combining formulas with the User data type allows you to create complex logic to determine which Users to assign, or even have your worksheets change based on changes to data in other worksheets.

How to Input Formulas in User Cells

You can create formulas in User cells the same way you would input a formula anywhere else – by typing directly in the cell or by using the formula bar above the worksheet header.

In order to use formulas in User cells, Strict must be turned off. For a formula to output a User value, your formula must reference an existing User cell as an output and not the explicit text string that matches the User value.

In the example below, we've created a reference table in the header region that links a painter number with a corresponding user, expressed as a User data type value. Then, in the table region, we use VLOOKUPs to assign a name to each room based on the painter number.


If we were to change our painter roster and assign a different person as Painter 1, we could change the value in C3 only and the associated rows in the table would automatically update.

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 "Tamara Tech" instead of referencing Cell B3, the value would not appear as a User despite Column C being set as the User 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 article on the User data type to learn more about assigning users in your workbooks.