Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Lookup?

Options
Jsmythers
Jsmythers ✭✭
edited 12/09/19 in Archived 2017 Posts

Ok, I'm stumped! I want to have a drop down list of employees no problem, but then I want a cost associated with each employee for time and money management. Such as:

drop list Johnny

Johnny makes 35.00 per hour

he clocks in for 6 hours

outcome 35 x 6= 

So I have the time, I have the hours and minutes, I just dont know how to connect people to by the hour from a drop down list

 

 

Comments

  • Shaine Greenwood
    Options

    If you're wanting to use the LOOKUP function for this, you'll need to create a lookup table in your sheet. This can be something as simple as putting people in the first position (index 1) of the table and their wages in the second position (index 2). You can then LOOKUP the wage based on the name, for example:

    =LOOKUP("Johnny", [Assigned to]1:Wage5, false)

    The trick is that Smartsheet doesn't work with time values, they're all treated as text unless you're using decimals. If you want to multiply the wage by the number of hours, you'll need to make sure that your time is at 6.00 for 6 hours, or 5.50 for 5 hours and 30 minutes.

    You can then make a formula that multiples this by the wage lookup. For example:

    =Hours1 * LOOKUP("Johnny", [Assigned to]1:Wage5, false)

    You'll need to play around with these example and adjust them for the column names in your sheet.

    One additional trick you can implement if you're not getting the results you need is to use the VALUE function. VALUE will convert a number that is treated as text into a numeric value for formulas to use. Details on this (and our other functions) can be found in our Help Center: https://help.smartsheet.com/articles/775363-using-formulas#value

This discussion has been closed.