Building a formula to connect multiple cells between sheets

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.

Find more posts tagged with

Sales

Formulas

Project Management

Cell linking

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
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?