Pull Most Recent Invoice Data for An Account Using INDEX/MATCH/VLOOKUP/MAX (Or Other)
Greetings!
Our team uses a Smartsheet to track monthly invoices for each client. Each invoice date is listed separately for each client (see attached). We are interested in populating in another Smartsheet the date of the last invoice. For some clients, it might be 02/28/2022, for others 12/31/2021, or other dates for other clients.
Is there a formula in Smartsheet that can pull for each unique client its last invoice date, even if the client name is listed multiple times?
Thank you,
John
Answers
-
I should add that the client names are not in alphabetical order. The Smartsheet is sorted by invoice date, then Client name.
-
Hi @John Crick
Yes! You can use a MAX(COLLECT formula to pull the Max Date, based on criteria you specify in the COLLECT function.
ex:
=MAX(COLLECT({Date Column}, {Company Column}, Company@row))
This would need to be put in a Date column in your second sheet, and it's assuming that you have all the client names listed down one column in your Company column already.
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Success! Thank you for the great solution!
John
-
Wonderful! I'm glad I could help. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Genevieve, circling back with a modification to the inquiry above. I would now like to add to the search criteria the last time a client was invoiced for a specific product (see attached). How can this be accomplished?
Thank you,
John
-
Hi @John Crick
This is where the COLLECT function is so handy... all you have to do is add in the new Column and new Criteria into your formula:
=MAX(COLLECT({Date Column}, {Company Column}, Company@row, {Product Column}, Product@row))
or
=MAX(COLLECT({Date Column}, {Company Column}, Company@row, {Product Column}, "Product"))
Let me know if this makes sense!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!