Idea on connecting two Smartsheets?

Matt Penta

I have two sheet that i want to reference and update each other. They both follow similar formatting, with one being more of a log/reference for the other. I would like one list to dynamically update the other. How would i do this? 

Items list - I have a items list that will show an overview of purchase orders to be done for a project. I then have a sheet set up to be a log for the other. Any Ideas on how to reference the other?

I want to say something along the lines of IF the reference column is populated, take the information from the corresponding reference in another sheet. EX: If i select A001, I want it to pull the information from A001 on another sheet. This way i can dynamically update both sheets with information. We sometimes have multiple events with the same reference number. 

A001

A002

A003

A001

A004

Andrée Starå

Hi Matt,

Yes, we'd use cross-sheet formulas for this use case. VLOOKUP or a combination of INDEX/MATCH. 

Would that work for you?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Matt Penta

I am currently using Vlookup. I will upload some screen shots if needed. I need to find a way to automate a remaining quantity list. 

Sheet 1 has general details in a easy to read manner for users to view

Sheet 2 uses vlookup to autofill most of the information from sheet 1, but also contains individual purchase orders for more detailed tracking. 

I need the Sheet2 purchase orders to reduce the Remaining Quantity in Sheet1. I know how to do this if I were to only have a single purchase order per component, but I am unsure how to do it with multiple purchases for the same component over a time period. I want to set up a system so that when a purchase order is entered into Sheet 2, it automatically changes the remaining quantity on Sheet1. Currently, my only idea is to make a third sheet that pulls that data and does the calculation. 

For Example, I have 10 Of Item A001 on an items list.

I purchase 3 on 9/1/19

I purchase 4 on 10/1/19

I will purchase 3 on 12/1/19

I need to automatically reduce the Remaining quantity amount with each purchase if possible. In summary, I need to be able to create a running sum for each reference number in an items list.

Matt Penta

I think what i really need to be able to do is to create a running sum, so that i can automatically deduct from the total quantity and get my remaining quantity. 

Example: A001 has a total purchase order of 10, 

A001 Order for 3 on 8/1/19

A001 Order for 4 on 9/1/19

A001 Order for 3 on 12/1/19

 

How do i create a formula/process in which i can Automatically deduct those amounts from the total purchase order on the items list to create my current remaining total. I need to set this up to work for potentially 80-100 different items that may be broken down into several different purchase orders like above. I know that i could use Vlookup for a single entry, but not for multiple or to create a running sum as orders are entered into the list.