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

Lindsay AR

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.

Lindsay AR

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