How to highlight co-activity per week

Tanguy Madel

Hey there,

I have a list of activities, with start and end date, and areas of work.
Areas are stored in a column as multiple dropdown choice.

I would like to identify the co-activity : if multiple tasks happen the same week in the same area.

Do you have an idea for that ?

The solution might be a flag or conditional formatting in the source sheet, or could be displayed in a report or portal.

ps : I have created the columns 'Start week' and 'End week' that might help…

Paul Newcome

In that case you would need to insert 6 helper columns and then parse the entries out along these columns on each row. There are a number of parsing solutions already provided here in the Community that should help you get that squared away. My suggestion would be one that utilizes the SUBSTITUTE function.

Then you would be able to use HAS functions in your COUNTIFS to check for overlap comparing the multi-select column to the helper columns.

ionam

To identify co-activities occurring in the same week and in the same area of work in your Smartsheet, you can consider the following approach, which uses a combination of columns, formulas, and potentially conditional formatting:

Steps to Identify Co-Activities:

1. Create Helper Columns:
   • Start Week: If you haven't already, ensure that your "Start Week" column calculates the week number of the start date. You can use WEEKNUM function or simply extract the start date week. You likely already have this based on your message.
   • End Week: Similarly, ensure that the "End Week" column is correctly set up.
2. For example, in the "Co-Activity Flag" column, you could use a formula like this:

   =IF(AND([Start Week]@row <> "", [End Week]@row <> ""),        IF(COUNTIFS([Start Week]:[Start Week], [Start Week]@row,                    [Areas of Work]:[Areas of Work], CONTAINS([Areas of Work]@row)) > 1,            "Co-Activity", ""), "")

• This formula checks if there is more than one task in the same area for the same start week. You may need to adjust the areas checking condition according to how your dropdown selections work (using CONTAINS or another method based on your entries).

1. Conditional Formatting:
   • You can add conditional formatting rules to highlight co-activities. For example, set a rule to change the row colour to red if the "Co-Activity Flag" column contains "Co-Activity". This will visually distinguish those activities from others.
2. Using Reports:
   • If you wish to display these co-activities in a separate report or portal, create a report that pulls records where the "Co-Activity Flag" is set to "Co-Activity". You can filter your report to show only these rows, making them easy to review in a dedicated view.

Implementation Summary:

• Add help columns for week identification.
• Use the COUNTIFS formula to flag co-activities based on the same area and week.
• Apply conditional formatting for quick visibility.
• Optionally create a report to keep track of co-activities.

Notes:

• The CONTAINS function may behave differently based on how the dropdowns are set up. If dropdowns allow multiple selections, you may need to handle comparisons carefully.
• Always ensure compatibility with how your specific fields and dropdowns are set up in the Smartsheet for efficient filtering.

Tanguy Madel

Thanks a lot @ionam ,
I have tried it, but I'm struggling with some error messages : #INCORRECT ARGUMENT SET and #INVALID OPERATION

No results for the moment.

I keep you posted once I've managed to get it solved.

ionam

If you have access to AI - chat GPT or Co Pilot etc that might be able to help you.

Paul Newcome

Are you able to provide some screenshots for context?

Tanguy Madel

After a couple of tries, it ended up with the following results : the formula seems to work sometimes with the multiple dropdown, but sometimes not…

answer @Paul Newcome , info @ionam

Paul Newcome

How many areas of work could potentially be selected within a single cell?

Tanguy Madel

Up to approx 6 entries simultaneously, within a list of 30 entries

Paul Newcome

In that case you would need to insert 6 helper columns and then parse the entries out along these columns on each row. There are a number of parsing solutions already provided here in the Community that should help you get that squared away. My suggestion would be one that utilizes the SUBSTITUTE function.

Then you would be able to use HAS functions in your COUNTIFS to check for overlap comparing the multi-select column to the helper columns.

Tanguy Madel

Thanks a lot for your tip @Paul Newcome, I'll give it a try in the coming days and let you know.