Can you use an If...Then Formula to populate multiple columns?

Nicole Davis

Hi!

I am looking to create a formula that will auto populate three columns depending on what region is chosen.

For Example:

If the region chosen is the Northeast then I want Person 1, 2 & 3 to auto populate.

If the region chosen is the Mid-Atlantic then I want Person 1, 4 & 5 to populate...and so on.

Is this possible? I tried the vlookup but it was too row specific.

Thank you!

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Do you have the people listed out in a grid that can be referenced somewhere?

bcwilson.ca

@Nicole Davis I did exactly what @Paul Newcome suggested.

I use something similar for purchase approvals to route based on the department that created the request

Simply create a Matrix, let's call it "Notification Matrix" and define Person 1, 2 3 for each region.
1. Make sure those columns are set up as "Contact Lists" in both the Matrix and in the Sheet
Do a vlookup for your SD VP from the Matrix using the Region as your look up
1. something like "=VLOOKUP([Region]@row, {Notification Matrix Range 1}, 2, false)"
2. Repeat for 2 and 3 to get your Director and RVP
Right-click and make those Column Formulas
1. also, lock the cells so no one can modify the formulas

And you are done..

You can now use workflow rules with those contacts as well..

So if you get a big order in the Southeast you could have a rule to Email the RVP based on dollar value, etc.

Or if an invoice remains unpaid for X many days it could use workflow to escalate using the contacts

If you are not using it for workflow they can simply be text fields

Nicole Davis

@bcwilson.ca & @Paul Newcome Thank you both for the very quick suggestion. I did have a table, however deleted it when I thought I was on the incorrect path. I will go back in and try this!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.