Lookups used to create a relationship

We're evaluating Smartsheet for project management. The business is commercial real estate. Projects involve matching a Client firm to a specific Property address, and that property also has an Owner. So if we have a Projects sheet, a Clients sheet, an Owners sheet, and a Properties sheet, we would need to tie these together.

For example: If we create a new Project row in the Projects sheet, it will have a Client column. We want to reference the client from the Clients sheet, and possibly pull in another column from the Clients sheet, like Phone Number, or Contact Person Name. Just like in a relational database. So then if the Contact's name changes in our Clients sheet, it automatically changes in our Projects sheet.

Is VLOOKUP designed to support this kind of usage? Are there any risks or downsides to treating sheets like relational tables?

Thanks.

Find more posts tagged with

Accepted answers

All comments

Andrée Starå

Hi David,

Yes, or you could also use a combination of INDEX and MATCH.

What would work best depends on your specific structure and more.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Would that work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

David Swearingen

Thanks for the quick response! Looking at INDEX, perhaps I don't understand it, but why would anyone ever use that given that if I drag a (referenced) row, its index value changes. This seems dangerous to me...

As for MATCH, it also does not feel "air-tight" to me, it seems like if people re-arrange columns, then my references to those columns would change.

Why won't VLOOKUP do what I want?

Thanks again.

Andrée Starå

Happy to help!

It's the other way around. INDEX/MATCH is the secure one and VLOOKUP can more easily be manipulated (depending on the ranges of course)

Also, the VLOOKUP break if you move columns, INDEX/MATCH does not.

Make sense?

David Swearingen

Trying this now. But my clients will never be able to use this, they are not technical. I can't figure out the syntax, and I'm an experienced programmer.

I must say, AirTable makes it easy to include these "foreign key" types of references. This is the only thing so far I've found in SmartSheet that is -- so far -- odd and difficult to implement.

Andrée Starå

Yes, it can be a little off before you get used to it.

Happy to take a look!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Andrée Starå

Not sure I follow!

What will your clients need to do? Do they need to add the formulas?

David Swearingen

If a project manager is populating a sheet with a new row that represents a new project involving Property Owner "ABC Corp.", then if they have to compose one of these formulas in order to reference "ABC Corp." in another sheet, then that's going to confuse and agitate them, too many parentheses, quotations, commas, normal people can't deal with that, they just want a picklist of companies, and they'll be asking me why I selected a tool that can't incorporate a simple picklist. Hopefully I'm wrong and there's an easier way...

Andrée Starå

Is it an intake process that they would add the row to?

Can you describe the process / structure in more detail?

Here's an excellent explanation and shortcut for INDEX/MATCH by @cleversheet

https://community.smartsheet.com/discussion/63841/hot-tip-index-match-formula-shortcut

Copy/paste this to quickly build these formulas:

=IFERROR(INDEX(ReferenceSheetPickContentColumn, MATCH([CurrentSheetMatchColumn]@row, ReferenceSheetPickMatchColumn, 0)), "")

Then just double-click the concatenated-word segments, replace the outer ones using the ReferenceAnotherSheet link from the Formula Help panel that automatically appears [except when looking within a sheet*], and the inner one by just typing the column name.

In the reference sheets I replace "Range 1..." with "Pick[NameOfContent/MatchColumn]" and tap the applicable column heading.

* - People usually think of using INDEX/MATCH between sheets, but there are slick occasions to use it within sheets, too.

David Swearingen

Hopefully this clarifies what we are trying to do. There could be a one to many relationship between Clients and Projects (same client could have multiple clients).

Thanks.

Screen Shot 2020-01-09 at 2.02.19 PM.png

Andrée Starå

Ok.

Depending on how often new clients are added there are two methods.

Copy the new client name and add it to a dropdown list for the Client Firm Name in the Projects sheet
Input the name and make sure it's spelled correctly

Both options can be set up to get the Client Phone number and more.

What do you think?

Paul Newcome

IN addition (referencing other people having to enter the formulas)

There are a few options, but if they are being added to a sheet that already has existing rows with the formulas, Smartsheet uses an autofill feature to automatically pull those formulas from the row below/above it depending on where the new row is being added.

orad

The two methods suggested by Andrée above from 3 years ago, basically means that there is no table relationship support and that information needs to be copied around and maintained manually, which is a nightmare concept for someone with a dev background like me. I was wondering if after three years there's any better support now for table relationships?

Thank you.

Paul Newcome

@orad What exactly are you wanting to accomplish?

Guy Price

Just hooking up to this thread, as I am trying to figure out if SS offers relational database functionality. I am a Notion user as well as a SS user/consultant. In Notion, I can create columns in my database/sheet that relate to data contained in other databases/sheets. For example, in my time/expenses tracker in Notion, I can search, select, reference the project that my activity relates to (from my separate Projects database/sheet), and I can also separately reference the task that my recorded activity relates to (from the separate Tasks database/sheet) - see screenshots from Notion.

Is this possible in Smartsheet? If so, how? Is VLOOKUP, or INDEX/MATCH like-for-like functionality?

Many thanks @Paul Newcome and/or @Andrée Starå - and @orad is this Notion example the kind of functionality that you are looking for?

orad

@Guy Price Yes, just basic relationships like in your example that allows linking tables and navigate between entries.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.