Building a formula to connect multiple cells between sheets

Y. Czerski

I understand that Smartsheet is not automation tool, but would like to make team's work easier without having to enter lots of information manually. 

Situation is that that we have two files: pipeline and quote. Once the quote is completed, there is a series of Values, about 40, which need to be called up on the Pipeline sheet. All of those 40 values will always be located on the same column and row of the template/quote sheet.

I am looking for an easy way to connect the 40 pieces of information without manually having to link each of those pieces of information to the cell in the pipeline. I can just link the whole row because I need to connect only certain cells, and their order is different on both sheets. 

I've been trying to build VLOOKUP formula for that, but so far I am failing miserably. 

Any help or general direction would be highly appreciated. Thank you in advance. 

J. Craig Williams

First, VLOOKUP() should not be used if the order of the columns may change. Much better is using INDEX(...,MATCH()) functionality.

Second, can you further explain this statement:

All of those 40 values will always be located on the same column and row of the template/quote sheet.

Third, is there a reason why you aren't just moving the rows from Quotes to Pipeline? 

(I can think of about half-dozen valid reasons off the top of my head, but I want to know YOUR reason, because sometimes, there isn't one)

...

To use INDEX(..,MATCH()) across sheets, you will need to have something unique to look for. A customerID or quote ID usually suffices.

The formula will look something like this on the Pipeline sheet.

=INDEX({Quotes | A COLUMN}, MATCH(UniqueID@row, {Quotes | UniqueID}, 0))

This will return the value a A COLUMN where the UniqueID's match.

You'll need to set up each of the 40 columns one at a time. Depending on size, you may run into Smartsheet's limitations on referenced cells.

It is easier to build and easier to break if the column order is the same. If that can't be guaranteed, one reference per column is the way to go. It took about an hour to see up a 20 column system like this.

I originally did this for a more complex problem, the communication between the two sheets needed to be partially two way.

You can read about that here:

http://ronin-global.com/2018/02/22/smartsheet-secure-your-sheets-with-x-sheet-references/

...

Lastly, if you don't really need the data in the Quotes sheet (which is where it will need to be maintained) or if the data is intended to be 'static' after the Quote phase, then Zapier may be an option. It would allow part of the row to be moved over the new sheet, especially if there wasn't a row there before.

I hope this helps.

Craig

Paul Newcome

You'll need to set up each of the 40 columns one at a time. Depending on size, you may run into Smartsheet's limitations on referenced cells.

 

Correct me if I am wrong, but I thought INDEX/MATCH could be used horizontally? I personally haven't figured out exactly how that would work as I don't use it often.

 

If it can be used horizontally then that would mean each column wouldn't have to be set up individually?

 

Or am I just way off base?

J. Craig Williams

INDEX(...,MATCH()) can be used on any range, so horizontal works too.

But what is the item being matched? We can't access column name or ID.

My understanding from the OP is he is trying to retrieve a limited number of cells from a particular row, but not all of them. 

Perhaps showing customer phone number and address (and others) that was entered on the Quotes sheet.

Before X-Sheet references, I wrote this to post with a cross sheet lookup that may be pertinent.

http://ronin-global.com/2017/04/22/an-alternative-to-nested-ifs/

Craig