Formula Needed


I am trying to see if there is a formula that will look at the Cells in row 1, and if matched to the any of the weeks in column Week Of, will return the corresponding deliverable value in Column1. Attached is the clip of my smartsheet. Does anyone know if there is a formula that can do this? I have tried using IF, INDEX, MATCH, and have had no luck.

I tried the below formula given to me and think it is close, but the cells show up as blank when I entered the formula in it. However it looked like it worked for the person who gave this to me, so I am not sure what I am doing wrong. Are there any other suggestions?

=IFERROR(JOIN(COLLECT([Column1]:[Column1], [Week Of]:[Week Of], @cell = "Aug Wk1")), "")

Here are some screenshots from my sheet for reference.

Thank you!


Best Answers


  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓

    Hi Crystal, I think part of the Join function is missing?

    Try this:

    =IFERROR(JOIN(COLLECT([Column1]:[Column1], [Week Of]:[Week Of], @cell = "Aug Wk1"), ", "), "")

    Hope this helps and that you have a great day,


  • Crystal Panning
    Crystal Panning ✭✭✭✭
    Answer ✓

    I figured out there needed to be a space between the Wk and 1 in Aug Wk 1. Thank you!

  • Crystal Panning
    Crystal Panning ✭✭✭✭

    Hello! I believe you helped me a little while back on this formula and it worked, but the weirdest thing just happened. It was working fine and all my information was showing up in the cells they were supposed to, and then when I went into the sheet today, the formula was still in place but none of my information was and can't figure out what changed since I didn't change anything. Would an enhancement have changed what type of formula should be used? Is there another formula that would work the same? Not sure why all my cells are blank now when everything was populating just fine previously! Any help is appreciated.

    =IFERROR(JOIN(COLLECT([Column1]:[Column1], [Week Of]:[Week Of], @cell = "Aug Wk1"), ", "), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!