Retrieving last month's value

BrigitteM

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

Jason Tarpinian

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.