Cross Sheet Joining Automation

estradamee

Hello Community,

I am attempting to make a few helper sheets try to consolidate information into one row.

Background: I have multiple sheets that have hundreds of parts/items from a range of vendors. I am hoping to implement a checkbox that initiates a purchase request next to each item.

My initial stab at this is when they need to be ordered, the checkbox trigger will copy the items to a new raw data sheet.

I then use a separate combined data sheet which I want to reference and join the items from the same vendor together. I was able to use a JOIN/INDEX formula to make the consolidation occur, but I would like to automate the new requests (via checkbox on the source sheet) to copy to the raw data sheet then consolidate into a single row on the combined data sheet.

If anyone has any ideas how to consolidate entries into the Raw Purchase Data sheet automatically to the Combined Purchase Data sheet, I would greatly appreciate this.

Thank you Smartsheet Community!!

I have attached some screenshots.

Devin Lee

Hey @estradamee,

I'm curious why you need it on one row? Is something happening to that row or is it just for organizational purposes? If it's just for organization a Report would be a better option for you. With Reports you can Group by Vendor and any information you need added together such as cost can be added using the Summary option. A condition set to your checkbox can also be set up to help filter the orders.

estradamee

@Devin Lee,

Full disclosure - once the rows are consolidated, I planned to copy them to a purchase request sheet. I considered directly moving the individual items line by line to the purchase request sheet, but I thought that would get out of hand quickly since dozens of items are ordered weekly from multiple vendors and that purchase request sheet has built in approval requirements. I wanted to group and consolidate the items from the same vendor into a singular line that can be reviewed by approvers.

I have looked into reports, which I believe are definitely powerful tools for tracking and organizing material automatically and aesthetically, but unfortunately they can't be included in sheet formulas or have approval automation.

The approval issue is essentially what I hope to integrate with just a check box from a BOM. An employee checks boxes for the program they require parts from, and those items are consolidated by vendor to one or two requests.

Devin Lee

@estradamee

Still trying to wrap my head around the whole thing but from what I can tell there are multiple sheets that contain parts that can be ordered (Raw Purchase Data). You want to use a checkbox to initiate the order which sends the requests to a single sheet (Combined Purchase Data). From there another sheet references those requests to consolidate orders by vendor so purchase approval can be made (Consolidated Purchase Data).

Raw to Combined

Make an automation to copy the row based on a checkbox trigger and clear the checkbox so additional orders can be made. If other information needs to be filled out each time you can add those to the clear list as well. Another option is to create another automation as a confirmation request in the sheet it's moved to via an update request triggered on when new rows are added.

Combined to Consolidated

The Consolidate sheet will need to have a list of all the vendors possible. From there in each column reference the Combined sheet with the formula below. We will need to have a Helper column for line breaks. Accomplish this by creating a column "Helper" and just put in two dashes with a line break between them (Control+Shift) into Helper1

=JOIN(COLLECT({Reference 1}, {Reference 2}, Vendor@row), SUBSTITUTE(Helper$1, "-", ""))

Reference 1 is whatever column you are trying to pull in (This will be a new reference for each column

Reference 2 is the Vendor column [Vendor:Vendor]

Notes

• In the Raw Purchase Sheet you might want to create a count for pending orders so people can see that number go up when they send orders to the Combined sheet.
• In the Combined sheet you can also add an ordered check box column or something similar to remove items off the consolidated list. This would alter the COLLECT portion of the formula by including any other columns for example:

=JOIN(COLLECT({Reference 1}, {Reference 2}, Vendor@row, {Reference 3}, =0), SUBSTITUTE(Helper$1, "-", ""))

Reference 3 is the checkbox column to remove the item

elicos

Were you able to figure it out?