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
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!