Lookup Columns Between Sheets

ChristoffBotes

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

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

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

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

Thanks again Paul - sure, let me do that!

ChristoffBotes

Hi there, 

I've created some dummy data now.

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

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

Paul Newcome

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

Andrée Starå

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'm utilizing cell-linking for this workaround. The main sheet is linked to the dropdown/list sheet.

Please see the attached link/screenshot for more information.

Example of dropdown/list

Would that work?

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

Best,

Andrée

Paul Newcome

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

ChristoffBotes

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

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å

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

https://community.smartsheet.com/discussion/comment/167316#Comment_167316

Hi Andree,

Can you please explain how this is done? the short gif is too fast, and I wasn't able to find an explanation.

Thanks in advance!