How do I run this report?

I have a sheet where there are several different "Assigned To" columns and then a "Project Status" column.

I need to create an employee workload report. Each report should only show that employee's assigned to tasks that are in their current status. For example:

Deron's report should show the row Project Name and Project Status. For example:

- (Row 4) AR DOT 040860 - Don Tyson Pkwy. Ext. (2025), 7_Market Lead Review
- (Row 5) 080619 HWY 60 (2025), 8_Final Review / PE Review
- (Row 8) XNA Access Road F 090069 - Utility Crossing, 5_Relocation Notes Need Field Notes
- (Row 9) Benton AR - Hwy 229 Development, 7_Market Lead Review

I am wondering what options I have to generate this information. Reports/formulas/data mesh process?

Thanks!

Find more posts tagged with

Formulas

Reports

Accepted answers

LeelaLodhi03

Hi @Lucinda Bugbee ,

Since your columns represent different roles and your Project Status shows the current stage, the key is to identify who owns the project at that stage.

You can do this by creating a contact-type helper column (e.g., “Current Owner”) using a formula that returns the person from the relevant column based on the Project Status (assuming your status values are consistent).

You can use the following formula in the helper column and convert it to a column formula:

=IF([Project Status]@row = "7_Market Lead Review", [Market Lead]@row, IF([Project Status]@row = "2_Conflict Analysis/Site Visit", [Conflict Analysis/Site Visit Assigned To]@row, IF([Project Status]@row = "3_Conflict Review QA/QC", [Conflict Review QA/QC Assigned To]@row, IF([Project Status]@row = "5_Relocation Notes Need Field Notes", [Relocation Notes Assigned To]@row, IF([Project Status]@row = "6_Engineering", [Engineering Assigned To]@row, IF([Project Status]@row = "8_Final Review / PE Review", [PE Final Review Assigned To]@row, ""))))))

Once that’s in place, you can build a report filtered by Current Owner = [Employee] to see their active workload along with Project Name and Status.

Hope this helps! 😊

All comments

LeelaLodhi03

Hi @Lucinda Bugbee ,

Since your columns represent different roles and your Project Status shows the current stage, the key is to identify who owns the project at that stage.

You can use the following formula in the helper column and convert it to a column formula:

Once that’s in place, you can build a report filtered by Current Owner = [Employee] to see their active workload along with Project Name and Status.

Hope this helps! 😊

Lucinda Bugbee

This absolutely worked! Thank you so much for your help! You have made me life a lot easier today!

Lucinda Bugbee

I spoke too soon! This works to filter by person but when I do the reporting I still have to show all the columns which doesn't streamline the view. You don't know if Deron in the first row is in the market lead task or conflict analysis/site visit task.

I assume I need to create a formula for Current Task also and then report on these 2 columns. Can you help with that? I am trying to decipher your formula now.

Thanks!

Lucinda Bugbee

OMG… never mind. I just need to report the Current Owner and the Project Status. That is what I get for overthinking this for 2 weeks!

LeelaLodhi03

Glad you got it sorted!
If you found helpful, please show some love by marking it as Insightful 💡 or Awesome ❤️, and feel free to Vote Up ⬆️.

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.