How to select a last added value from a column

Jasmine Hassan

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!

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.