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)
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives