Formula to Find Last Non-Empty Cell In a Row
I am using a sheet that tracks counts of different products by location, which gets updated in 30-minute intervals (product location by row and time intervals by column). My goal is to use a function to automatically pull the most recent count for each product, which would require the function to check for the last non-empty cell in a given row and count that number.
I've found a function that appears to work just fine in Excel/Google Sheets, but always comes up as #UNPARSEABLE when used in Smartsheet. Formula, below, links to the original page that I found it on which explains how the formula works.
Does anyone know how to help me make this work?? Does Smartsheet not have the capability to do anything like this?
Thank you in advance!!
Best Answer
-
Oh do I have a solution for you! Newbie here, but lmk what I can do to help. There's always an easier solution.
=IF( [SUM of DATA in ROW] > [other DATA in ROW], [SUM of DATA in ROW] )
Check out the brown row. It has 53 columns. (I have the sums reaching forward and back a bit, referencing other cells) The formula has the range my data is in, a ">" operator (condition), and the same data range PLUS 1. IF this condition is met the sum is posted, if it is not, it is blank.
Answers
-
Hi there @Michael Hatchell ,
I feel that based on the Screenshot above, and the original intended use of the Excel Formula, I may have found the Formula you are looking for:
=INDEX([Primary Column]:[Primary Column], COUNTIFS([Primary Column]:[Primary Column], NOT(ISBLANK(@cell))))
From my Screenshot, you can see that in the Primary Column, the last number entered is 20, and when using the Formula above, it has returned the latest entry in that Column.
Although this Formula may seem larger than it's Excel variant, and although there may be a more simplified Formula for the same purpose, this should provide the result you are looking for.
Here are the Articles I used to create this for if you would like a detailed breakdown on how they can be used:
INDEX: https://help.smartsheet.com/function/index
COUNTIFS: https://help.smartsheet.com/function/countifs
NOT: https://help.smartsheet.com/function/not
ISBLANK: https://help.smartsheet.com/function/isblank
@Cell: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
Let me know if you have any questions!
Regards
Sean
-
Oh do I have a solution for you! Newbie here, but lmk what I can do to help. There's always an easier solution.
=IF( [SUM of DATA in ROW] > [other DATA in ROW], [SUM of DATA in ROW] )
Check out the brown row. It has 53 columns. (I have the sums reaching forward and back a bit, referencing other cells) The formula has the range my data is in, a ">" operator (condition), and the same data range PLUS 1. IF this condition is met the sum is posted, if it is not, it is blank.
-
I have something similar and your formula works. Expect it say cell value 23 is blank. 20 will still want to be returned.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!