What function would I use to reference a different cell in a row based on what is in another cell?


I recently asked a question similar to this and got an answer. However, I am having to go about it a different way, this is all on me, and now I am up against another wall.

I am trying to reference a cell based on what is in the same row but a different cell. I am not doing any calculations so I cannot use @cell.

Posted below are the two sheets I am trying to link. The first picture is the column I reference for finding crew names. Based on what is there, the function references the row number of that column through a helper column. My issue is that it won't recognize a name in a list. The solution given to me previously was to use an @cell in a HAS function. I am no longer using a SUMIF formula to collect the proper row number so I can't use the @cell any more.

My new function is to use a MIN(COLLECT( function. This works well because I can set up a drop down menu for multiple jobs assigned to a crew member and affect it by using the n-th limiter in the MIN function. This works as long as there is only one name to look at.

Could I somehow use the HAS function to recognize a name in a list of names? I have tried just using a a cell reference for the criteria as well as a direct text to look for in the COLLECT function and it does not work.

Thank you

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!