Report same start dates for assign to across multi sheets

Jon Barto

Is there a way to get a report that shows if I assign a task to a contact on one sheet and then on another sheet they are assigned to start that project the same day.

Here is example

Sheet 1

Bank Job Smith Plumbing Start date 3-15-23

Sheet 2

City Building Smith Plumbing Start date 3-15-23

So I would like to have a report that shows when a vendor is assigned to start jobs on the same day.

Is that possible?

Jon Barto

Or even if there is a way to write a formula to search for sheets in a workspace to find cases when the Assigned to and start date on one sheet overlap the same assigned to and start date on another sheet.

We use one sheet (construction schedule) for every job. But all the sheets are in the same workspace.

Genevieve P.

Hi @Jon Barto

You could generate a Report that filters by a specific person's name, then Groups by the Start Date column and uses the Summary to count how many rows exist for that person across all sheets with that Start Date.

The only thing is this will show you all the data where they only have 1 row (meaning no duplicates) as well as multiple rows:

If you only have two sheets, then in this case I would have a Flag column with a COUNTIFS formula that will flag any row that has a matching Assigned To person and Start Date in the other sheet:

=IF(COUNTIFS({Assigned To}, [Assigned To]@row, {Start}, Start@row) > 0, 1)

Cheers,

Genevieve

Jon Barto

Thanks Genevieve,

Question do you have to link or call out the other sheets? or does it find the sheet automatically?

For example say the other project sheet is not "made yet"

We have a master schedule template (we save as new and rename to whatever that job name is) when we get a new job. So each job has the same columns rows, ect.

So would that formula search all sheets? (in a workspace, in our account?)

Genevieve P.

Hi @Jon Barto

You can set a Report to scope an entire Workspace, so as new sheets are added/created then they're automatically included in the Report.

However for formulas, since the sheet doesn't exist yet, the {references} can't exist either. You would need to manually create a new formula for each new sheet.

Cheers,

Genevieve

Jon Barto

Thanks Genevieve! That was alot of help