Remotely fill in a new row on another sheet.

ty.sjomeling100911

Hello,

I have two separate sheets that need to be connected. One is an inventory sheet of all raw materials in stock, and the other is a record of all raw materials delivered.

If we receive a delivery of a type of raw material we already have some in stock of, the quantity of that material is simply updated in the inventory sheet. Each row of the inventory sheet contains each raw material we have. To calculate the quantity of that raw material, the quantity cell uses SUMIFS to look through the delivery sheet to find all the rows with that material's name and some the quantities delivered. 

However, we may also receive a shipment of a new material that we did not previously have in stock. Therefore, new material does not yet exist in the inventory sheet. This new material will be entered as usual into the delivery sheet. 

How do I

1. recognize that this new material name does not exist in the inventory sheet

2.Then, automatically fill in a new row in the inventory sheet using the information of this material from the delivery sheet. 

Please let me know if I can clarify anything. Thanks!

 

Andrée Starå

Hi Ty,

1. We would use a VLOOKUP or similar to check if it's available and if it's not it could say something like "Not Found - New Material?" How is the material identified? Do you use an item number?

2. The third-party service Zapier is an excellent option for this scenario. Is that an option for you? It would make it possible to copy the new material information to the inventory sheet.

Would that work?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

1. I feel like that as long as the material id's are unique, you wouldn't need a clunky VLOOKUP. The CONTAINS function should work for this.

 

=IF(CONTAINS(Material@row, {Other Sheet Material Column}) = false, "New Material - Add to other sheet")

.

2. I agree. Just don't do what I did and go all crazy and accidentally use up all of your monthly tasks within a day or two. Haha

ty.sjomeling100911

Thank you Andrée,

Yes, got it on 1).

For 2), I DO have access to Zapier. I will check it out. 

Thanks!

ty.sjomeling100911

Andrée, 

Do you know just how to approach integrating Zapier with Smartsheets?

 

Andrée Starå

Excellent!

Happy to help!

Andrée Starå

Yes, I work with Zapier all the time in my business and for my client's workflow needs.

How can I help?

ty.sjomeling100911

Hi Andrée, 

Like I said we have two spreadsheets:

1. Inventory (How much of each item name/dimensions we have in stock at the moment)
2. Delivery (Each row is a single shipment of new material we have received)

I need to do two things with smartsheet and Zapier: 

1. If we receive a shipment of material that we already have some in stock of, for example titanium 12*34*12, then this material/dimension’s row in the inventory spreadsheet is increased by the number delivered. I have already solved this problem using just smartsheet. 
2. If we receive a shipment of material that we have not received before, this entered material’s name/dimensions will not exist yet in the inventory spreadsheet, just in the delivery spreadsheet. I need to have Zapier recognize that this material/dimension combination does not exist in the inventory spreadsheet and then add a row in the inventory spreadsheet with its information. 

This trouble I am having is getting Zapier to scan through the inventory sheet to see if this material/dimension combination exists or not. 

How do I program this test? 

Once this test is programmed, getting Zapier to actually add the row is pretty simple. 

Thanks!

—Finn

Andrée Starå

Happy to help!

We would add a so-called helper column to the delivery sheet that would, for example, check a checkbox when the combination of information isn't already in the sheet. That would then trigger a Zapier Zap to copy that row to the inventory sheet.

Would that work?