Idea on connecting two Smartsheets?

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

Find more posts tagged with

Manufacturing

Sheets

Formulas

Reports

Conditional Formatting

Cell linking

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…