Need to get the last not blank text value in a column

Hi,

I have an inventory sheet with transactions of check-ins and check-outs. So far it has just been collecting the quantity of checked-in and checked-out to get the overall in stock quantity. Transactions are done via a form that input a new row into bottom of the sheet, with count numbers being updated at the top.

This has been working great. However, I have new items being added that are only check-in and check-out rather than quantity. I need to be able to show the latest status of these items. I pasted an example of the sheet below.

The red values are what should show in the status location above blue line based on the most recent created date.

I have tried formulas with collect, index, max, but I just can't get it. I am able to add a helper column or two, but I can't add a helper column for each item, as their will be too many.

Best Answer

  • Lindsay AR
    Lindsay AR ✭✭✭✭✭
    Answer ✓

    For anyone else who may have the same question, I figure it out.

    I added a row id column (name 'ugh' because I was very frustrated by this point) and reference the row id rather than the created date. The final formula is as follows:

    =INDEX([column name]:[column name], MATCH(MAX(COLLECT([row id]:[row id], [column name]:[column name], ISTEXT(@cell))), [row id]:[row id], 0))

    so my formula looked like this using the columns in the screen shot above with the addition of ugh (row id):

    =INDEX(b:b, MATCH(MAX(COLLECT(ugh:ugh, b:b, ISTEXT(@cell))), ugh:ugh, 0)) I updated the formula for each column collection I needed

Answers

  • Lindsay AR
    Lindsay AR ✭✭✭✭✭

    Here are some formulas I have tried:

    =JOIN(COLLECT([item column]:[item column], Created:Created, MAX(Created:Created))) ---->this results in no value if the column at row is blank

    =JOIN(COLLECT([item column]:[item column], Created:Created, MAX(Created:Created), [item column]:[item column], NOT(ISBLANK(@cell ---->this results in no value if the column at row is blank

    =INDEX(COLLECT([item column]:[item column], [item column]:[item column], NOT(ISBLANK(@cell))), 1) ---->this only works for items being added to top of the sheet (this is not an option due to other users)

  • Lindsay AR
    Lindsay AR ✭✭✭✭✭
    Answer ✓

    For anyone else who may have the same question, I figure it out.

    I added a row id column (name 'ugh' because I was very frustrated by this point) and reference the row id rather than the created date. The final formula is as follows:

    =INDEX([column name]:[column name], MATCH(MAX(COLLECT([row id]:[row id], [column name]:[column name], ISTEXT(@cell))), [row id]:[row id], 0))

    so my formula looked like this using the columns in the screen shot above with the addition of ugh (row id):

    =INDEX(b:b, MATCH(MAX(COLLECT(ugh:ugh, b:b, ISTEXT(@cell))), ugh:ugh, 0)) I updated the formula for each column collection I needed

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!