Delivery Set Up

Michael Kingsbury

Hey everyone,

I have a question. My set up:

Multiple line items with different Work Package Names. 

Once the work package is complete we mark it for shipping based on date and time to our job sites. 

I would like to have a sheet that would consolidate all the work packages (name of the work package) to one row based on that date and time.

Example:

From this

WP#     Shipping date Shipping Time Jobsite

1         10/2/18             7am               ABC Company

2         10/2/18             7am               ABC Company

3         10/2/18             7am               ABC Company

To this

Shipping date  Shipping Time Jobsite                WP#'s

10/2/18             7am               ABC Company     1,2,3

 

Thinking about using Zapier somehow and "run it" The day of in early am but it would be nice to populate automatically from last minute changes and stuff.

 

Any ideas?

 

Thanks

 

Paul Newcome

Have you looked into a JOIN(COLLECT( formula using cross sheet references? You use the JOIN to bring everything together, and the COLLECT allows you to specify criteria within ranges. Something along the lines of...

 

=JOIN(COLLECT({WP# Range}, {Shipping Date Range}, @cell = [Shipping date]@row, {Shipping Time Range}, @cell = [Shipping Time]@row, {Jobsite Range}, @cell = Jobsite@row), ",")

Michael Kingsbury

Paul, I am going to look at that. That seems like the best option for me. Ill give you an update after I test it out.

 

Thanks