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

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…