Show Last Non-Blank Text Cell in Row
![Nrob14](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Hi Everyone,
Hoping you can help because I'm stumped.
In the 'Most Recent/Current Project' column pictured below I am trying to add a formula that will show the text within right most cell that is not blank.
I've tried two methods that work in Excel, but the formulas don't seem to be usable in SmartSheet.
In Excel, I've used both formulas below with success.
=LOOKUP(2,1/(B2:E2<>""),B2:E2)
=INDEX(B3:E3,COUNTA(B3:E3))
I've see several posts about similar questions in this community but they seemed to be related to finding numerical values or dates, not text which is what I need. But if I have missed one I apologize and please point me in the right direction!
Any help would be appreciated!
Nick
Best Answer
-
@Nrob14 I haven't tested this... but I think it should work if you replace the names of the columns.
=index(collect([<<first column>>]@row:[<<last colmun>>]@row, <<first column>>]@row:[<<last colmun>>]@row, NOT(ISBLANK(@cell))),count(collect([<<first column>>]@row:[<<last colmun>>]@row, <<first column>>]@row:[<<last colmun>>]@row, NOT(ISBLANK(@cell)))))
Answers
-
@Nrob14 I haven't tested this... but I think it should work if you replace the names of the columns.
=index(collect([<<first column>>]@row:[<<last colmun>>]@row, <<first column>>]@row:[<<last colmun>>]@row, NOT(ISBLANK(@cell))),count(collect([<<first column>>]@row:[<<last colmun>>]@row, <<first column>>]@row:[<<last colmun>>]@row, NOT(ISBLANK(@cell)))))
-
That worked!!
Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!