Formula Needed

Crystal Panning

Hi,

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.

Smartsheet2.PNG

smartsheet3.PNG

Thank you!

Crystal

Jason Albrecht

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"), ", "), "")

Crystal Panning

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

Jason Albrecht

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"), ", "), "")

Crystal Panning

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

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"), ", "), "")