Formula Needed

Question

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.

I tried the below formula given and it worked perfectly up until I looked at my sheet today and all my cells where the formula is are blank and I am not sure what changed from one day to the next and why it was working and now it doesn't.

smartsheet3.PNG

Are there any other suggestions?

=IFERROR(JOIN(COLLECT([Column1]:[Column1], [Week Of]:[Week Of], @cell = "Oct Wk 1"), ", "), "")

Genevieve P. · Accepted Answer

Hi @Crystal Panning

There are a few things we can check here.

First, I would suggest removing the IFERROR from around your formula. The blank cells are coming in because there's an error, but it would be helpful to know what that exact error is. Then we can use the Formula Error List to identify what may be the cause.

Secondly, I see that you have a formula pulling in the data to your [Week Of] column. I would recommend double checking that there are no errors in this column in any cell, since the entire column is being looked at. One error (even if it's on a different row than what the formula is looking for) will create a domino effect and bring an error into this formula as well.

Finally, you could see blank cells because the formula isn't finding a match for what's in your quotes and what it's searching for. Double check that your reference (ex. "Oct Wk 1") is spelled exactly the same as what appears in your  [Week Of] column. An additional space or a removed space will cause the formula to not find a match.

Let me know if any of this helped! If not, it would be helpful to see the formula open in the screen capture (double click the cell).

Cheers,

Genevieve