nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Reports Based on "Assigned to" Across Sheets

aschneiderheinze1025

I am creating templates that our marketing team can use for different categories of projects that the director will use to create a "task sheet" for new projects. Columns that the templates have in common (including Assigned to) will be named the same. A project owner managing the task sheets will add contacts to "Assigned to" columns (one contact per task).

I read somewhere that I can create a report for a member of the marketing team (e.g., video producer) that will pull all of the tasks across the task sheets, so long as the task sheets are saved in the same folder in a workspace.

This report will list the tasks and a few other columns (again, that are the same across the templates), so the individual will have a comprehensive list of tasks across projects in one place.

Ideally, everyone who is assigned tasks and has a report just for them would be able to add information in the report, but I don't think they all have a license, and most if not all are internal users.

So, two questions:

Did I read it correctly (in an online article I can't find now) that a report can pull the tasks assigned to a specific person across sheets (as those sheets are updated and even as new sheets are added) as long as they are saved in the folder?
An internal user would need a license to edit the report (if that user has an email with the same domain), correct? If they don't they will essentially just have viewing access.

Find more posts tagged with

Reports

Accepted answers

Paul Newcome

You said:

"Putting them in the same folder would help us avoid having to update the reports every time a new planning sheet is added."

Actually, if you put them in the same folder and reference the folder, you WILL have to update the report every time a new sheet is added.

Referencing the workspace is what automatically adds the new sheets.

You asked:

"how do I avoid pulling anything from all those other sheets?"

You can apply a filter in the report based on sheet name. So if you have consistent naming conventions such as "Project Name Planning Sheet", you can filter the report to only pull rows from sheets where the sheet name contains "Planning Sheet".

An additional filter of the [Assigned To] being any of "Current User" means you don't have to create a bunch of different reports. Just one report that is filtered to show rows from "Planning Sheet" sheet names where [Assigned To] is whoever is viewing the report.

All comments

Alyson_SmartU

Hi aschneiderheinze1025 - I think you would be better served by creating a 'Current User' report, then setting the report source to be the workspace (not the folder). SmartU has indepth material on building reports along with tips for building and distributing them. If neither of the below are something you are looking for then I would search for an online article about a Current User Report.

Ondemand: https://smartu.smartsheet.com/path/core-app-path-elearning
Virtual Instructor Led: https://smartu.smartsheet.com/the-builder-path-instructor-led-sessions

All the best,

Alyson E.

Paul Newcome

First: The scope of the report would be a workspace, not a folder. Sheets added to a folder do not automatically get added to a report, but sheets added to a workspace do. One thing to note with this is that ALL sheets within the workspace will be pulled into the report. I suggest including a filter on the sheet name so that only rows from the various task sheets are pulled in. This would be in addition to a filter where [Assigned To] has any of "Current User".

Second: That assumption is mostly correct. the seat type "Viewer" is being updated to "Contributor". This will allow unlicensed internal users to also comment and attach. If they need to adjust cell data, then yes. They will need to be a licensed "Member".

Having said that… If you have access to the Premium App Dynamic View (DV), you can also apply a "Current User" filter to that which would allow internal members to edit cell data without a license. This also has a potentially added benefit (if data security is a concern) of not having to share the underlying data to users for them to see it in a DV like you do with a report.

aschneiderheinze1025

I understand the difference between a workspace and folder in that workspace. We have a workspace for all of the department, which includes our marketing group. We are building a marketing project management (MPM) sheet that will list all marketing projects. Each marketing project will have a project plan sheet that will be linked to the MPM sheet (cell link, and some cell references).

I thought if I saved the planning sheets in a folder, I could generate a report for each team member (and partner within the organization) that would pull the [Assigned to] from all of those reports into that one report, so the individual can to their report and see all of their assigned tasks. Putting them in the same folder would help us avoid having to update the reports every time a new planning sheet is added.

If the report pulls from the entire workspace—-which includes all other groups such as program development, instructional design, hiring/onboarding, etc.—-how do I avoid pulling anything from all those other sheets?

Note: Currently, the project planning sheets in marketing are the only sheets with [Assigned to] column. This is a new thing we are doing for this group, but I can see applying a similar method with other groups. I guess what comes to mind is either applying a filter in the report that just shows Marketing tasks —- but I guess it wouldn't be bad to include any other tasks they are assigned by other groups, too. The individual can then decide what they want to filter in their own report.

The key thing I am looking to do is avoid having to update the report every time a new project planning sheet is added. The report should automatically include tasks from any new project planning sheet added, without having to add the sheet to the report manually.

Am I thinking about this correctly?

Paul Newcome

You said:

"Putting them in the same folder would help us avoid having to update the reports every time a new planning sheet is added."

Actually, if you put them in the same folder and reference the folder, you WILL have to update the report every time a new sheet is added.

Referencing the workspace is what automatically adds the new sheets.

You asked:

"how do I avoid pulling anything from all those other sheets?"

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.