We have a form set up for collecting information from scientists performing runs by different projects. We need to be able to supply material lot information to clients separately by project numbers.
I tried to use the following forums to perform the index/collect/distinct formulas which didn't work for me when referencing a separate reference sheet:
https://community.smartsheet.com/discussion/90798/using-distinct-function#latest
https://community.smartsheet.com/discussion/87186/list-all-unique-values-from-a-column-within-one-smartsheet-in-a-separate-smartsheet
When I would use them as-is (Obviously substituting my references), it would say unparseable or incorrect argument.
I do not need the data to be parsed out into separate rows, it can go into 1 cell. Currently we have been exporting the data to excel and organizing it manually for each project or using macros to do the same work, but would like to automate it in smartsheet since the data is already parsed into smartsheet from the scientists.
To add a layer, I need this function to only work if a specific Project # is found. Sometimes the cells include multiple projects:
Current set-up:
Main sheet collected by a form used as reference sheet: Lot Documentation
Secondary sheet to organize data: Project Lot Documentation
So for example, one of the material lots captured is water on the reference sheet "Lot Documentation". The exact name of the column is "Ultrapure Water Lot".
On the Project Lot Documentation sheet, If the project column is "INFCS", I want all unique lots of water to be listed where INFCS is at least 1 of the projects in the "Project #" column on the reference sheet. I will have to repeat this process for 100s of unique materials and repeat this project lot sheet for each project.
If we can automate this, it will save a lot of time. Any help is appreciated.
