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!

Find more posts tagged with

Manufacturing

Formulas

Support

Comments

Paul Newcome

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)))

Jasmine Hassan

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

Paul Newcome

What is the exact formula you have in the Test column?

Jasmine Hassan

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

Paul Newcome

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?

Jasmine Hassan

Sure. I can share a copy of of the sheet. Can i have your email address please?

Thank you.

Jasmine Hassan

Hi Paul,

I have added you to the sheet. Thank you

Jasmine

Paul Newcome

Excellent! I'll take a look.

Sign in to join the conversation!

Quick Links

Trending discussions

Looking for assistance with a formula
Hi all, I am new to formulas and functions in Smartsheet and have a large sheet that I would like to summarize using an "average" formula/function. I need to include at least 2 separate criteria to filter correctly and I am having trouble combining the avg and collect functions as suggested in the functions list. I have…
Need help in formula function
I'm not able to get this formula to function in Smartsheet. The material ordered date is 9/3/2025. Am I missing something to get the date rage to populate? =TEXT([Material Ordered Date]@row +28,"MM/DD/YYYY")&"-"&TEXT([Material Ordered Date]@row +42, "MM/DD/YYYY")
I am trying to find a value in a table based on 2 criteria.
I am trying to find a value in a table based on 2 criteria. One of the values is dynamic from a drop down menu, while the other is static value in a column. The first value is found in column "Building type" and the other is found in Resource type. The table I am referencing is also shown. The result would be shown in the…