Item Tracking

L_123

I am building an item tracking report with a helper column. There are 14 different items, and I want the helper column to post 1 (or anything it honestly doesn't matter what it posts) if it is the most recent action for an item for the purpose of building a report. I've got an autocreated time column and autocreated index to use already. I am struggling with the formula for this however. Any help would be appreciated.

Shaine Greenwood

Hi Luke,

You might try adding a checkbox column to your sheet with a function that checks the box if it's the most recently submitted item, for instance:

=IF(MAX([Auto-Created Column Name]:[Auto-Created Column Name]) = [Auto-Created Column Name]@row, 1)

The above formula will check the box if your "auto created" cell on a specific row is equal to the max value in the range of the entire column.

You could also refine this more with the COLLECT function, for example:

=IF(MAX(COLLECT([Auto-Created Column Name]:[Auto-Created Column Name], [Other Column]:[Other Column], "Item 1")) = [Auto-Created Column Name]@row, 1)

This formula will compare the cell to the max value of other auto-created cells that also have "Item 1" text in a column titled "Other Column."

NOTE: with the above examples, change the column names to match the names of the columns in your own sheet.

After adding the checkbox column and placing a formula similar to the examples above in the checkbox column, you can alter your report's WHAT criteria to gather all rows that have the checked box.

More on these functions can be found in the help center:

IF: https://help.smartsheet.com/function/if

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

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

@row: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

L_123

Thanks. I ended up going with something very similar

=IFERROR(IF(MATCH(MAX(COLLECT([Action Index]:[Action Index], [Laptop Issued]:[Laptop Issued], =1, Laptop:Laptop, Laptop3)), [Action Index]:[Action Index], 0) > MATCH(MAX(COLLECT([Action Index]:[Action Index], [Laptop Returned]:[Laptop Returned], =1, Laptop:Laptop, Laptop3)), [Action Index]:[Action Index], 0), 1, 0), IF(COUNTIF(Laptop:Laptop, Laptop3) > 1, 1, 0))

 

I appreciate the help.