Lookup Columns Between Sheets

ChristoffBotes · 05/10/19

Good day all

I'm a newbie on the community.

I'm trying to deal with a scenario which I thought was very common and assumed Smartsheet would have an easy way to deal with.

The scenario is: we have a very simple sheet for managing customers. This sheet includes a customerID, business name, primary contact person, postal address, physical address and a few other columns.

Now I want to add a sheet for orders placed with us by our customers. This would then include an order date, sales person name, total number of items, delivery method and maybe a status field to track our delivery. One other piece of information is crucial - the customer that placed the order.

How can I, from the orders sheet, look up the customer from existing customers we have registered in the customers sheet?

This is very similar I guess to lookup columns in Microsoft SharePoint or relationships in a database.

How do we achieve this with Smartsheet?

Many thanks in advance.

Cheers

Christoff

Paul Newcome · 05/10/19

There are a few ways to go about this, but the best solution is going to depend on your exact setup.

Could you post some screenshots with "dummy data" in them showing your current setup along with how you would like the desired outcome to be displayed?

ChristoffBotes · 05/10/19

Thanks Paul,

The orders sheet I have not done yet - as I don't know how to link/lookup the customer from the customers sheet. I can create it as far as I can and will then post screenshots.

Ideally, the orders sheet would have a drop-down (or similar) column that gets its values (options) from existing data in the customers sheet (like the list of customer names that would be unique).

Does that help so long?

Thanks

Christoff

Paul Newcome · 05/10/19

It does help. What I mean by "dummy data" is manually entering data into the orders sheet so that it looks the way you want it to. We can then build off of that to automate things. I use this method regularly when building out linked sheets because it helps me visualize the end result as I put everything together.

ChristoffBotes · 05/11/19

Thanks again Paul - sure, let me do that!

ChristoffBotes · 05/11/19

Hi there,

我现在已经创建了一些假数据。

In short, our process is as follows:

In this scenario, most of our customers are farms (like cattle ranches etc.).
We have a sheet to record customer data. Sheet called "Customers".
Each customer will only ever have a single row in this sheet with a unique customerCode and BusinessName (which is usually the name of the farm/ranch).
Then we have a separate sheet called CustomerOrders where orders that a customer places with is recorded here

What we're trying to achieve is: when I record a new order in the CustomerOrders sheet, I must be able to select the customer that placed the order from a list (like a drop-down column) where the list, is actually the customers that are stored in the Customers sheet. In this case, would ideally be the "BusinessName" column from the Customers sheet.

Screenshots attached.

Customers sheet with dummy data
CustomerOrders sheet with dummy data

Let me know what else I can add.

Thanks a million

Christoff

ChristoffBotes · 05/11/19

Hi, I've done that. Seems like my post with screenshots is under review before it will be published.

Paul Newcome · 05/13/19

Ok. I will keep an eye out for the email notification.

Andrée Starå · 05/15/19

You could use a VLOOKUP or an INDEX/MATCH formula, but then you'll need to keep the Dropdown updated from the Customers sheet or remember all of them.

A possible workaround could be to use cell-linking and select from the Customers sheet.

More info:

Here is a workaround that gives you the possibility to use lists of information and choose from that list inside a sheet (doesn't work with mobile, only desktop)

I'mutilizingcell-linking for this workaround.The main sheet is linkedto thedropdown/list sheet.

Please see the attached link/screenshot for more information.

Example ofdropdown/list

Would that work?

Let us know if you prefer the first suggested method instead.

Best,

Andrée

Paul Newcome · 05/15/19

This is what I was looking for. Glad you stepped in.

ChristoffBotes · 05/15/19

Hi there,

Thank you very much for the response.

Unfortunately, neither of those options seem like solutions.

On the first option, having to remember all our customers or manually keeping the drop-down list updated (aka hardcoded) is not feasible. Customers are too many to remember and ofcourse manually updating the drop-down list is exactly what we're trying to avoid. Certain people are responsible for maintaining customer data (mostly the customers) and it is just not feasible to require manually copying & pasting into a hardcoded drop-down list every time there's a change to customers.

The second option unfortunately isn't feasible either. Our agents/ staff work on the road, on the go, out of the office all the time. We are working off mobile 90% of the time.

Are those the only options?

Thanks

Paul Newcome · 05/15/19

Unfortunately the only way to accomplish what you are looking for WITHIN Smartsheet you will need some type of database that will require regular maintenance.

Andree may have some insight into 3rd party apps though...

Andrée Starå · 05/30/19

How did it go?

Did you solve it or do you still need help?

Is a third-party solution an option?

Best,

Andrée

eharow · 07/04/22

You could use a VLOOKUP or an INDEX\/MATCH formula, but then you'll need to keep the Dropdown updated from the Customers sheet or remember all of them.<\/p>\n

A possible workaround could be to use cell-linking and select from the Customers sheet.<\/p>\n

More info:<\/p>\n

Here is a workaround that gives you the possibility to use lists of information and choose from that list inside a sheet (doesn't work with mobile, only desktop)<\/em><\/p>\n

I'm <\/em>utilizing<\/em> cell-linking for this workaround. <\/em>The main sheet is linked<\/em> to the <\/em>dropdown<\/em>\/list sheet.<\/em><\/p>\n

Please see the attached link\/screenshot for more information.<\/em><\/p>\n

Example of <\/a><\/em>dropdown<\/em><\/a>\/list<\/a><\/em><\/p>\n

Would that work?<\/p>\n

Let us know if you prefer the first suggested method instead.<\/p>\n

Best,<\/p>\n

Andrée<\/p>","bodyRaw":"

You could use a VLOOKUP or an INDEX\/MATCH formula, but then you'll need to<\/pwa> keep the Dropdown<\/pwa> updated from the Customers sheet or remember all of them.<\/p>\n\n

A possible <\/pwa>workaround could be to use cell-linking and select from the Customers sheet.<\/p>\n\n

More info:<\/p>\n\n

Here is a workaround that gives you the possibility to use lists of information and choose from that list inside a sheet (doesn't work with mobile, only desktop)<\/em><\/p>\n\n

I'm <\/em>utilizing<\/pwa><\/em> cell-linking for this workaround. <\/em>The main <\/pwa>sheet is linked<\/pwa><\/em> to the <\/em>dropdown<\/pwa><\/em>\/list sheet.<\/em><\/p>\n\n

Please see the attached link\/screenshot for more information.<\/em><\/p>\n\n