Retrieving last month's value
Hi, I am trying to retrieve the previous months value based on a primary identifier.
I can set up my smartsheet to either move past values to another sheet or leave them all on a sheet.
The idea is when a user adds a new line item, with a primary identifier, the previous month's price can be automatically pulled into the row. the conditions im trying to put into a formula are
if primary identifier matches (using an @ row), pull value that was in the 'price' column for the most recent entry (based on 'as of date' column, it will be the last business day of the previous month) and put that value into the 'prior month, or most recent price' column
see screenshot of sheet setup below
Answers
-
I believe I'm following your situation, and if you add an auto-numbered column "Row ID", you should be able to use this formula:
=INDEX(COLLECT(Price:Price, [Primary Identifier]:[Primary Identifier], [Primary Identifier]@row, [As of Date]:[As of Date], <DATE(YEAR([As of Date]@row), MONTH([As of Date]@row), 1)), COUNTIFS([Primary Identifier]:[Primary Identifier], [Primary Identifier]@row, [As of Date]:[As of Date], <DATE(YEAR([As of Date]@row), MONTH([As of Date]@row), 1)))
It will collect the range of "Price" based on the "Primary Identifier", and only those that are earlier than the 1st of the "As of Date" month. The row identifier part of the INDEX function is then using the same logic, and will choose the nth row, before the 1st of the month within that collected range.
The first field of each Primary Identifier will be an error as there is no previous Price associated with it. You can wrap that in an IFERROR to have it look cleaner if you'd like.
And to clarify, this will retrieve the latest value prior to the first of the month. So it could be the previous month, or 5 months ago if none had been priced since.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!