Need to find all instances of value and return any non-blank cell from another sheet?
Hello Everyone,
I'm stumped and need some assistance. What I am trying to do is look for the value in the Cable Number column (Cable Number]@row) on the master sheet in the Cable Number Column on the Update Tracker (Update Tracker Cable}). There will be multiple times this value is in this column but some dates will be blank.
I then need the non-blank date pulled from Cable Pull Start Date ({Update Tracker CPSD}) on the Update Tracker to the Cable Pull Start Date on the Master.
This is the formula I am trying but it keeps returning blank values:
=INDEX({Update Tracker CPSD}, MATCH([Cable Number]@row, {Update Tracker Cable}, 0), 1)
Best Answer
-
Hi,
I just wrote this formula for you and I think it will work:
= IFERROR( INDEX( COLLECT( {Update Tracker CPSD}, {Update Tracker CPSD}, NOT(ISBLANK(@cell)), {Update Tracker Cable}, @cell = [Cable Number]@row ), 1 ), "" )
This is how the logic works.
- Begin collecting data from {Update Tracker CPSD}.
- Criteria: Make sure that the cell is not blank.
- Criteria: For each row, look at the corresponding cell in {Update Tracker Cable}, make sure that it is equal to the current row's [Cable Number].
- INDEX: Grab the first value that we found from COLLECT.
- If COLLECT didn't have any data, then it will throw an error. This means that the Cable number did not have a date, so let's just set the cell to "".
Let me know if you have any questions!
SSFeatures
SSFeatures - The browser extension that adds more features into SmartSheet.
- Automatic sorting, sorting with filters, saving sort settings
- Spell checking
- Report PDF generator that supports grouped and summarized reports
Answers
-
Hi,
I just wrote this formula for you and I think it will work:
= IFERROR( INDEX( COLLECT( {Update Tracker CPSD}, {Update Tracker CPSD}, NOT(ISBLANK(@cell)), {Update Tracker Cable}, @cell = [Cable Number]@row ), 1 ), "" )
This is how the logic works.
- Begin collecting data from {Update Tracker CPSD}.
- Criteria: Make sure that the cell is not blank.
- Criteria: For each row, look at the corresponding cell in {Update Tracker Cable}, make sure that it is equal to the current row's [Cable Number].
- INDEX: Grab the first value that we found from COLLECT.
- If COLLECT didn't have any data, then it will throw an error. This means that the Cable number did not have a date, so let's just set the cell to "".
Let me know if you have any questions!
SSFeatures
SSFeatures - The browser extension that adds more features into SmartSheet.
- Automatic sorting, sorting with filters, saving sort settings
- Spell checking
- Report PDF generator that supports grouped and summarized reports
-
That works!! Thank you so much!!
-
No problem!
Let me know if you have any other questions about how it works.
SSFeatures
SSFeatures - The browser extension that adds more features into SmartSheet.
- Automatic sorting, sorting with filters, saving sort settings
- Spell checking
- Report PDF generator that supports grouped and summarized reports
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!