Formula to pull the next populated cell in a column
I am trying to have a formula pull in data from the next populated cell, I currently have a formula that pulls in by row. This sheet has rows that get copied over from different sheets and sometimes there are blank rows so I am trying to have a formula that takes that into account.
My current formula is =INDEX({Work Order Range 1}, 1) which returns "372" trying to figure out a way to write a formula to return 645. Any help is greatly appreciated. Thank you,
Brandon
Answers

You would use something along the lines of
=INDEX(COLLECT(ID:ID, ID:ID, @cell <> ""), COUNTIFS(ID:ID, @cell <> ""))

I appreciate the reply. I am not wrapping my head around what criteria to use for the index collect portion of the formula. I just want to pull the next populated cell in that column. Thank you,
Brandon

Your ranges would all be whatever column you want to pull from, and the criteria in both the COLLECT and COUNTIFS functions are where the cell is not blank. Have you tried plugging in the formula with your own column names to see if it works?

I plugged it in this way:
=INDEX(COLLECT({Work Order Range 1}, {Work Order Range 1}, @cell <> ""), COUNTIFS({Work Order Range 1}, @cell <> ""))
This returns the 645 can I use this same formula in another cell to pull in the next number when it is added?
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!