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

  • SSFeatures
    SSFeatures ✭✭✭✭✭
    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.

    1. Begin collecting data from {Update Tracker CPSD}.
    2. Criteria: Make sure that the cell is not blank.
    3. 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].
    4. INDEX: Grab the first value that we found from COLLECT.
    5. 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

  • SSFeatures
    SSFeatures ✭✭✭✭✭
    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.

    1. Begin collecting data from {Update Tracker CPSD}.
    2. Criteria: Make sure that the cell is not blank.
    3. 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].
    4. INDEX: Grab the first value that we found from COLLECT.
    5. 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!!

  • SSFeatures
    SSFeatures ✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!