Resolve Multi-Select Choices into single lines

I have about 25 identical project schedule sheets. Each task has a flag indicating if it is a milestone that is also a change event (someone's job is changing). I'd like to create a sheet and calendar of all change events aggregated from all these sheets.

Columns are:

Workgroup Impacted (multi-select)

Project Name

Event Name

Date

I'd like to end up with an aggregated sheet (all 25 projects) that has a row for each individual workgroup selected and the project name, event name, and date associated with a change event. e.g. if Workgroup A and Workgroup B are both selected for Project 1 Event 1 on 30 APR, and in another project sheet Workgroup A and C are selected for Project 2, but for different change events, I'd like four rows:

Workgroup A, Project 1, Event 1, 30 APR

Workgroup B, Project 1, Event 1, 30 APR

Workgroup A, Project 2, Event 4, 15 JUN

Workgroup C, Project 2, Event 7, 01 SEP

Any ideas how to do this?

Is there a different approach that may work better?

Thanks for the assist!

Find more posts tagged with

Accepted answers

All comments

Mark Cronk

Hi @Ronald Moyer ,

I think you can get what you're looking for by creating a new report. Link all 25 of your project sheets to it. Filter by Milestone is checked. Add the columns you want displayed. Then convert the report to calendar view or link it to the calendar app.

Work?

Mark

Ronald Moyer

Thanks Mark -

Unless I am misunderstanding, that doesn't quite work. I am trying to get to the point where each individual workgroup has it's own row on the calendar. What I am getting looks like the attached picture.

Essentially I am trying to undo the multi-select.

I've considered using automation to copy the row to a table when Change Event? = flagged, or trying to write a formula that looks for the flag and uses some form of "HAS" or "CONTAINS" but keep running into dead ends.

Thanks for your input and help.

Ron

Ronald Moyer

I came up with a work around using the calendar (above) and various filters to isolate individual workgroups or projects on the calendar.

That may be enough to get us going.

If anyone has other ideas, I'd appreciate it.

Ron

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.