How to select a last added value from a column

Jasmine HassanJasmine Hassan ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
07/31/19 Edited 12/09/19

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!

2019-07-31_12h12_58.png

Comments

  • Paul NewcomePaul 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 HassanJasmine Hassan ✭✭✭✭✭

    Hi Paul,

    Thank you for your help and support. But unfortunately the formula is not working as expected sad

    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

    2019-08-01_10h29_47.png

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

  • Jasmine HassanJasmine 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 NewcomePaul 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 HassanJasmine Hassan ✭✭✭✭✭

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

    Thank you. 

  • Jasmine HassanJasmine Hassan ✭✭✭✭✭

    Hi Paul,

    I have added you to the sheet. Thank you :)

    Jasmine

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Excellent! I'll take a look.

Sign In or Register to comment.