Best Of

While this thread is a bit old, I thought I'd share a different approach that I settled on to solve this problem. My use case has a limited number of team members (TMs) that can be assigned to a task, and any number of TMs can be assigned to each task. I needed a way to show a list of these assigned TMs without taking up as much space as was needed to show a listing of full names.

I originally attempted a solution like described above to grab the first names using the LEN and MID functions that could accommodate up to 4 TM's being listed. I would have liked more to be safe, but the vast number of helper columns (and the long indecipherable function that I used to avoid so many helper columns) was inelegant and used up columns senselessly in a sheet with many other useful columns and the potential for 1000s of rows. The SS cell limit was a potential concern.

I opted instead to create a separate table where we would list the TM's "System Name" (as would appear in a contact list) and their "Display Name" which could be their first, last or even a preferred nickname. I found other uses for this table to store other TM data that we may also use. The key here (and consider this before you attempt this idea yourself) is that this approach can only be used for a relatively limited number of TMs, perhaps 15 or so people on a team. You'll see why in a sec.

Then, I used the SUBSTITUTE function to look up each TM System Name in the contact column and swap in the TM Display Name. For example, if the list of TMs in the other sheet had only 1 name, then you'd have:

=SUBSTITUTE([Assigned To]@row, INDEX({TM System Names}, 1), INDEX({TM Display Names}, 1))

Obviously, that's not very useful, so I nested 15 of these together...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Assigned To]@row, INDEX({TM System Names}, 1), INDEX({TM Display Names}, 1)), INDEX({TM System Names}, 2), INDEX({TM Display Names}, 2)), INDEX({TM System Names}, 3), INDEX({TM Display Names}, 3)), INDEX({TM System Names}, 4), INDEX({TM Display Names}, 4)), INDEX({TM System Names}, 5), INDEX({TM Display Names}, 5)), INDEX({TM System Names}, 6), INDEX({TM Display Names}, 6)), INDEX({TM System Names}, 7), INDEX({TM Display Names}, 7)), INDEX({TM System Names}, 8), INDEX({TM Display Names}, 8)), INDEX({TM System Names}, 9), INDEX({TM Display Names}, 9)), INDEX({TM System Names}, 10), INDEX({TM Display Names}, 10)), INDEX({TM System Names}, 11), INDEX({TM Display Names}, 11)), INDEX({TM System Names}, 12), INDEX({TM Display Names}, 12)), INDEX({TM System Names}, 13), INDEX({TM Display Names}, 13)), INDEX({TM System Names}, 14), INDEX({TM Display Names}, 14)), INDEX({TM System Names}, 15), INDEX({TM Display Names}, 15))

While this formula is long, its much easier to decode than the formulas needed to parse out sections of text in the multiselect contact string.

A few notes:

1) In the Team Member sheet, only the first 15 names will be swapped using this formula.

2) Apparently, the SUBSTITUTE formula cannot handle a "null" for the "old_text", so the Team Member sheet must have at least 15 rows of names in it (otherwise it returns an error which I found to be difficult to correct with a simple solution). If your team, like mine, has less than 15 people, I just put filler info in for the remaining rows and anticipate swapping in real names as the team grows.

3) Any name in the [Assigned To] column that isn't found using this lookup, will simply remain as-is using the System Name.

4) BTW... SS needs a function that would return the Nth element of a multiselect (Like INDEX does with a range). There's a function (COUNTM) to count the number of elements in the multiselect, so why not?

5) While I'm dreaming, I can't tell you how often I use nested SUBSTITUTE functions. Maybe SS could tweak the SUBSTITUTE function to accept ranges (in addition to individual values) for the second and third arguments. The SUM function can do it, so why not? Then my formula would reduce to the simple:

=SUBSTITUTE([Assigned To]@row, {TM System Names}, {TM Display Names})

*sigh*

Try switching the arguments for the dates. You are currently filtering for rows with a start date in the future and a finish date in the past. You want to search for rows wiht start dates in the past and finish dates in the future.

Hi @Carmen H,

Formulas for Totals:

for each label, substitute {Worksheet 1 - Meetings Chaired} cross-sheet reference for {Worksheet 1 - Attended as a member} etc. as needed for each row. To reference H2 2025 swap out "H1 2025" for "H2 2025" etc. as needed across multiple columns.

=SUMIFS({Worksheet 1 - Meetings Chaired}, {Worksheet 1 - Time Period}, "H1 2025", {Worksheet 1 - Scorecard Category}, "Governance")

You asked about adding additional columns, if you intend to report or visualize this data you are better off with a column for each time period. However, if you want to summarize the year within one column you can create a visually appealing summary with this trick:

1. Add a helper column with a cell that has "-[carriage return]-" (see highlighted column, on your keyboard you'd type - then Alt+Enter then -
2. Combine two of the formulas mentioned above, along with a SUBSTITUTE formula for the carriage return. Here's the formula as an example for 2025 Combined row 2

="H1 2025: " + SUMIFS({Worksheet 1 - Meetings Chaired}, {Worksheet 1 - Time Period}, "H1 2025", {Worksheet 1 - Scorecard Category}, "Governance") + SUBSTITUTE($[Helper Column for carriage return]$1, "-", "") + "H2 2025: " + SUMIFS({Worksheet 1 - Meetings Chaired}, {Worksheet 1 - Time Period}, "H2 2025", {Worksheet 1 - Scorecard Category}, "Governance")

Hope this all helps, do let me know if I've missed the ask, I'd be happy to take another look.

Adam