Pulling Column Data from Multiple Rows for Document Generation

Alex.Charles

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?

Samuel Mueller

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)

Samuel Mueller

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)

Alex.Charles

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.

Samuel Mueller

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)