INDEX? I may be making this too complicated but I am drinking from the firehose right now.

Chris Rolando · 04/26/22

I have a sheet that is fed from a form. In one area of the sheet there is a column called Additional Notes.

Most of the time this is left blank. But when it is filled in there is a column next to it that has a check box showing if the issue was closed (checked) or if the issue is still open (unchecked).

I am creating another sheet which is an "Open Issues" sheet. On this sheet I would like to have a list of Issues, that do not have a check mark, and I would like to also bring over the technicians name (Column), the Date (Date Column) and the Store Number (Store Number Column).

I have been on this one for a few days, gone though Vlookup, Vlookup/If, INDEX... and I am chasing my own tail.

L_123 · 04/26/22

Number of entries doesn't really matter for this, you can max out the rows in smartsheet and it will still work, albeit slowly.

I added some comments on the sheet I shared that will hopefully explain this further, I recommend looking into the collect formula page to understand how it works.

https://help.smartsheet.com/function/collect

L_123 · 04/26/22

Thought this was worth making an example:

https://app.smartsheet.com/b/publish?EQBCT=cd775f8cee2c4405a2d2e19d20beab71

Chris Rolando · 04/26/22

Thank you so much for the answer. My problem is that I don't exactly understand it.

The screen shot below is what I am trying to return.

The sheet I am pulling from has the notes I am trying to pull in column 21. The check mark to show if it is resolved is in Column 22. The technician is in column 3.

This is a portion of the sheet I want to pull from. The data I want to draw is from the "Service Notes" column, ONLY in the case that there is a note in there AND the checkbox to the right is not checked.

Does this help clarify my question?

Chris Rolando · 04/26/22

Further clarification:

The Store # Column has about 600 entries. I only want to show the ones that that have a comment in them.

L_123 · 04/26/22

Number of entries doesn't really matter for this, you can max out the rows in smartsheet and it will still work, albeit slowly.

I added some comments on the sheet I shared that will hopefully explain this further, I recommend looking into the collect formula page to understand how it works.

https://help.smartsheet.com/function/collect

Chris Rolando · 04/26/22

https://community.smartsheet.com/discussion/comment/327428#Comment_327428

Thank you. This did it and better yet, I now understand!

L_123 · 04/26/22

NP. Glad I could help. Really it would be nice if we could avoid the complexity of these type of solutions by referencing reports with formulas, but Smartsheet hasn't figured that out yet. (I assume it's easy to do, hard to do efficiently)

@Genevieve P.

INDEX? I may be making this too complicated but I am drinking from the firehose right now.

Best Answer

Answers

Help Article Resources

Categories