Item Tracking
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.
Comments
-
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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!