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?

Find more posts tagged with

Accepted answers

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)

All comments

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.