Summary Report

Tec

Hi,

In a typical sheet of say 10 tasks i have, where applicable, resource names assigned per row. I would like to add up all the named resources on a sheet and display them in one cell in my summary report. Is this possible?

Paul Newcome

Ok. You will need to create a Multi-Select Dropdown Column (or field in the Sheet Summary). Then you will want to enter the below:

=SUBSTITUTE(JOIN([Contact Column]:[Contact Column], CHAR(10)), ", ", CHAR(10))

The delimiter in a multi-select contact type column is ", " (comma space), but the delimiter in a multi-select dropdown type column is CHAR(10) (line break).

So we use the JOIN with the CHAR(10) to pull each contact cell together, replace the ", " in each cell with a line break, then drop them into a multi-select dropdown column to filter out duplicates.

Paul Newcome

Are you looking for a count of unique names, or if John Doe is assigned to three rows he would count three times?

Tec

Hi Paul,

Thanks for the reply but no.

John Doe and Jane Doe will be assigned to different tasks on different rows in my source sheet(s). What I'd like to do in my SUMMARY REPORT is show both John and Jane in one cell, if possible.

In other words "John Doe, Jane Doe" in one cell pulled from two different cells. Hope that make sense

Paul Newcome

Ok. You will need to create a Multi-Select Dropdown Column (or field in the Sheet Summary). Then you will want to enter the below:

=SUBSTITUTE(JOIN([Contact Column]:[Contact Column], CHAR(10)), ", ", CHAR(10))

The delimiter in a multi-select contact type column is ", " (comma space), but the delimiter in a multi-select dropdown type column is CHAR(10) (line break).

So we use the JOIN with the CHAR(10) to pull each contact cell together, replace the ", " in each cell with a line break, then drop them into a multi-select dropdown column to filter out duplicates.

Tec

Perfect - many thanks, just the job

Paul Newcome

Happy to help. 👍️

Mark S Krebs

Thanks for this @Paul Newcome . I have a strange thing occurring on a sheet. Im using a similar formula to the above (adding a collect to get all names from another sheet) and bringing the data into a contact column. Everything looks great then I save or refresh and converts back to Text.

goes from something like this:

to text.

Any advice? Thanks in advance!

Paul Newcome

@Mark S Krebs Can you copy/paste the exact formula you are using from the sheet directly to here?

Mark S Krebs

=JOIN(DISTINCT(COLLECT({Master Bidder list Range 2}, {Master Bidder list JobID}, [JOB ID]@row, {Master Bidder list Active}, =1)), CHAR(10))

Column Im bringing it into is a contact column with multi checked. If I change properties from contact to text then back again. They show as contacts then revert back to text upon save.

Master Bidder list Range 2 - is pulling from an email list which Ive tried as a Text and a Contact column but same behavior occurs.

Paul Newcome

@Mark S Krebs There is currently no way to use a formula to automatically populate multiple contacts within a cell. I'm not sure why it would change back and forth though.

@Genevieve P Have you seen something like this before?

Genevieve P.

Hi @Paul Newcome & @Mark S Krebs

I haven't seen the adjustment back and forth before, but Paul is correct, there currently isn't a way to bring multiple contacts into a contact cell using a formula. Please provide your feedback to the Product Team, here.

Mark S Krebs

@Genevieve P I can show you... Never seen it before but I can reproduce it. Im trying to collect all outside bidders back to intake so I can show their intake lines they bid on. We can do manually but wanted to automate.