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
-
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
-
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)
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!