Pulling Column Data from Multiple Rows for Document Generation
I currently have a tracker that records absences for individuals and assigns points per policy. Each absence instance for each individual is it's own row. I want Smartsheet to generate a PDF for an individual with each of the separate dates where "Points">0.
I have tried different combinations of functions, but I cannot seem to get the "Date" and "Points" to pull from previous rows into the helper columns to allow for the document generation. There could be 6+ instances, each requiring a column in order to pull into the document generation. There are also 1000+ unique "Personnel" in this sheet. Document generation would be done based on the most recent instance.
Something along the lines of: Collect "Date" & "Points" for "Personnel@row" for each instance where the Points>0.
Does anyone have a solution for this?
Best Answer
-
use index collect, soemthing like this.
=index(collect([column to return]:[column to return], [personell]:[personell], personell@row), 1, 1)
Then in the second column
=index(collect([column to return]:[column to return], [personell]:[personell], personell@row), 2, 1)
Answers
-
use index collect, soemthing like this.
=index(collect([column to return]:[column to return], [personell]:[personell], personell@row), 1, 1)
Then in the second column
=index(collect([column to return]:[column to return], [personell]:[personell], personell@row), 2, 1)
-
I appreciate it, Samuel. Is there any way to eliminate instances where the points assigned are 0? From what I'm seeing here, it will pull every instance where a person had an occurrence, regardless of whether or not it was 0.
-
add it to the collect piece of the formula
=index(collect([column to return]:[column to return], [personell]:[personell], personell@row, points:points, >0), 1, 1)
