HELP!

brhodes

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.

Jgorsich

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

brhodes

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

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

Jgorsich

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.