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)))
-
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?
-
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?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!