I have two sheets:
- Marketing Program Portfolio (MPP) in which each row is a different program code.
- Marketing Campaign Management (MCM) in which each row is a different campaign code.
A campaign can include one program or multiple programs. Each campaign has a status (e.g., Not Started, In Process, On Hold, At Risk, Completed, Canceled).
I set up a column in the MPP sheet with a cell reference formula that returns the campaign code(s) and an abbreviation of the status in MCM that pertains to the program code in the row.
EXAMPLE 1:
[Program Code] = "5004"
[Campaign Codes (Active)] = "0001 (NS)", meaning campaign code 0001 and "NS" referring to a "Not Started" status
EXAMPLE 2:
[Program Code] = "5010"
[Campaign Codes (Active)] = "0001 (IP), 0003 (AR)", meaning campaign code 0002 and "IP" referring to a "In Process" status and campaign code 0003 and "AR" referring to a "At Risk"
The MCM sheet is set up so each row is a campaign code, with a column for status, and a column that lists the program code(s) included in the campaign:
EXAMPLE 1:
[Campaign Code] = "0001"
[Program Code(s)] = "5004,5010"
[Status] = "In Process"
EXAMPLE 2:
[Campaign Code] = "0002"
[Program Code(s)] = "5008"
[Status] = "Completed"
EXAMPLE 3:
[Campaign Code] = "0003", current status is "At Risk"
[Program Code(s)] = "5010"
[Status] = "At Risk"
The Director of Marketing would ideally like a report, grouped by Program Code, that lists each campaign underneath, with the campaign status, owner, started date, etc.
I understand the relation between program codes and campaign codes/status is not set up in the sheet to accommodate this kind of report. I was thinking of two options:
- Using hidden columns in MCM sheet to "parse" the campaign codes and statuses, and then somehow using these hidden columns in the report, since they are still in the row with the program code.
- Create a new sheet that sets up the relationship better, with each row being a program code (even if there is more than one row for a program code) with the campaign code and status. However, I don't want to manually create this sheet, I wonder if there is a way to "dynamically" generate it with a formula that looks at the [Program Code] and [Campaign Codes (Active)] columns and adds this to separate rows in this sheet.
This new sheet would look something like this:
5004 0001 In Process Patty Smith 7/25/2026
5008 0002 Completed Hua Lin 7/1/2026
5010 0001 In Process Justin Owens 7/15/2026
5010 0003 At Risk John Doe 8/1/2026
I don't have any third-party tool or Bridge. This must be done completely just using Smartsheet.
Any suggestions on how to come up with a report that looks like this (grouped by Program Code, filtered if [Campaign Codes (Active)] is not blank, sorted chronologically and includes the campaign code, status, owner, and start date:
5004
— 0001 In Process Patty Smith 7/25/2026
5010
— 0001 In Process Justin Owens 7/15/2026
— 0003 At Risk John Doe 8/1/2026
