Find last Combination and Return Date
Hi
I want to be able to find a previous combination of an entry and return the date for that entry (not the created date, but the date in the date field)
I've been using the following to find the highest previous date but I want to integrate an extra criteria which is the last time a certain combination appeared.
=MAX(COLLECT(Date:Date, Date:Date, @cell < Date@row))
For example, when I add the last row for Manchester and Audit 1, I want the next cell to tell me when the last audit was completed, so in this case 05/07/2023. Ideally, also if there is no previous audit, enter None instead.
I've added a helper field to combine the Location and Type field into a single cell and then use that Helper column in the criteria but its not working.
Thanks
Answers
-
Hey @Simon Wilcock
Try this. If the system autonumber column isn't already in your sheet, you'll need that (Or, you can use the Created Date in it's place if you already have that. You would substitute [Row ID] with Created, in the formula below
=IFERROR(INDEX(Date:Date, MATCH(MAX(COLLECT(Date:Date, Date:Date, ISDATE(@cell), Location:Location, Location@row, Type:Type, Type@row, [Row ID]:[Row ID], @cell < [Row ID]@row)), Date:Date, 0)), "None")
Will this work for you?
Kelly
-
Great, this is good for me. Thanks Kelly 😀
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!