Building sheets that talk to each other

Gina Benninger

Hello,

I have two sheets I would like to reference data to and from and I understand how to us the =VLookup option BUT what I cant seem to get it to work.

I basically want one sheet that our shipping team updates to automatically update another sheet my purchasing team uses.  The columns have dates and check boxes so when something comes into our warehouse and the shipping team checks the box that it has arrived I want it to update the purchasing sheet to also automatically click that the PO arrived.  The PO number will be the value that matches.  Can this be done and if so HOW???

 

Chris McKay

Hi Gina,

I'd suggest using INDEX and MATCH together and then taking advantage of Smartsheet's cross-sheet references functionality.

An INDEX/MATCH formula works in the following way:

=INDEX(The range of values you want to return, MATCH(The value you are wanting to match,the range of values you want to check the match against,the search type))

I prefer INDEX/MATCH to VLOOKUP for a few of reasons. You'll notice I do not have to specify a column number. INDEX/MATCH doesn't need to specify a column for it to find a value. You give it a range and it'll look. It also doesn't need to have your match value in the leftmost column of the lookup range. Lastly (and probably most importantly), in Excel at least, INDEX/MATCH is significantly faster when you are dealing with larger datasets. I've not tested the theory in Smartsheet but knowing the way VLOOKUP works, it's likely the same applies.

So assuming you have:

• 2 sheets called Shipping and Purchasing
• a common PO Number column in both sheets
• a Shipment Arrived checkbox column in your Shipping sheet
• a PO Arrived checkbox column in your Purchasing sheet

Then, type out this formula in the PO Arrived column:

=INDEX({Shipment Arrived}, MATCH([PO Number]1, {Shipping PO}, 0))

The reason I ask you to type it rather than paste it is that the names inside the curly braces "{}" are named ranges referencing another sheet (in this case Shipping). You'll want to set these as you go.

Once you type =INDEX( you'll see a link to Reference Another Sheet. This will then bring up another dialogue box where you can select the Shipping sheet, click on the column header for the Shipment Arrived column and then name it (e.g. Shipment Arrived).

Once your formula is in, lock the PO Arrived column so users cannot inadvertently delete your formula.

Hope this puts you on the right track.

Kind regards,

Chris McKay