How to select a last added value from a column
Would love to get feedback on how to get a "Last added value" of a particular item. I have a list of items which keeps adding in the sheet as new rows. I need a formula that looks at a column to pull the latest value/entry made. What I want is a formula that updates automatically as I populate new rows.
Below is a screenshot that shows the columns I am working with. The value of the column “Closing Stock” I am trying to populate in a new row as “Opening Stock”. The goal is to capture the most recent entry of “Closing Stock” column based on the “Product Name” column.
Any suggestions?
Thanks!
Comments
-
To start, I would suggest using a Helper Column (we'll call it Row ID for this example). In that column we would use the following...
=COUNTIFS([Product Barcode]$1:[Product Barcode]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))
This will give you a row number that we can reference in an INDEX formula using a MAX/COLLECT to only look at row numbers with the same barcode and pull the highest one (the last row for that particular item)
=INDEX([Closing Stock]:[Closing Stock], MAX(COLLECT([Row ID]:[Row ID], [Product Name]:[Product Name], [Product Name]@row)))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
Thank you for your help and support. But unfortunately the formula is not working as expected
We added new columns (row ID & Test) to add the formulas. When we created a new line (row) for Mushroom Risotto with Fava Beans, the Test column became blank on the new row as well as on the earlier row. Please note that the earlier row had a value of 50 as closing stock.
Jasmine
-
What is the exact formula you have in the Test column?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
Thank you so much for your response. Here is the formula.
=INDEX([Closing Stock]:[Closing Stock], MAX(COLLECT([Row ID]:[Row ID], [Product Name]:[Product Name], [Product Name]@row)))
Jasmine
-
Hmm... Would you be able to share a copy of the sheet to me with any sensitive or confidential data replaced with "dummy data" so I can take a look?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Sure. I can share a copy of of the sheet. Can i have your email address please?
Thank you.
-
Hi Paul,
I have added you to the sheet. Thank you
Jasmine
-
Excellent! I'll take a look.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 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!