HELP!

Options

I am trying to match asset number hours that automatically come in via form and pull them into a separate data dump of current inventory.

I only want the highest value since the number of hours are reported daily.

Tags:

Answers

  • Jgorsich
    Jgorsich ✭✭✭
    Options

    Hard to advise without more information - at least a screenshot of what you have and a simulation of what you want.

  • brhodes
    Options

    I have a form that dumps the current hours into a sheet (second screenshot). I want to take the highest value and match it to the asset number on my current inventory (first screenshot).

  • brhodes
    Options

    =VLOOKUP([Asset Number]@row, {Hours_Column}, 5, false) is not working so Ive been stumped for awhile…

  • Jgorsich
    Jgorsich ✭✭✭
    edited 05/03/24
    Options

    Gotchya…

    Okay, what you want to do is a max(collect()) combination.

    https://help.smartsheet.com/function/collect

    https://help.smartsheet.com/function/max

    The collect formula will let you pull data that meets certain conditions (here, your asset number at a minimum, possibly also your created date if you want to do some graphs of accumulation/spend) and then your max formula will give you the maximum of the data that you pull.

    So, something like this would probably work for you:

    =max(collect({secondSheet_Hours},{secondSheet_AssetNumber},[asset number]@row))

    You can see, the collect() function is saying "pull me all the hours that are in the same row as where the asset number is X" and then the max function is saying "give me the biggest of those". You could add more criteria to the collect if you wanted to as well.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!