Formula to auto-populate a large sub-set of contact information data?

Hello,

I create a 'contact list' for each project, with ~20-30 people assigned to the project (column info pasted below). I wanted to create a "Master Contact List" which would have ~300 contacts pre-loaded in it, such that it starts as a blank template (no contacts added), but as you add a contact's name, the remaining columns flow in (their role, phone number, email, etc). Building out ~300 automated workflows would be a considerable level of effort, so I was wondering if anyone knew if there's a formula that would take less time?

Thank you!

Find more posts tagged with

Accepted answers

All comments

Ryan Sides

Hi there, are you looking to have your individual sheets populate the Master sheet? Or are you looking for your individual sheets to pull data from the Master?

SNickNBCUniUSH

@Ryan Sides - I would like either individual sheets to pull from the master, or for each project, use the master list (but the master list "starts" empty), and as you add 'Contact Name' for all 20-30 people on the project, the rest of the person's data then auto-populates.

Ryan Sides

I gotcha. Ok, in either scenario, build your Master list. In your project sheet, You can key off the user's name to pull in the rest of their data.

See explanation here: https://www.youtube.com/watch?v=-PoQkm1u47s

I used INDEX/MATCH to make this happen.

Is that what you were looking to do?

SNickNBCUniUSH

@Ryan Sides Ah! See I knew there was a simpler solution. Yes that's great - thanks!

Ryan Sides

You bet! Enjoy

SNickNBCUniUSH

@Ryan Sides for some reason I'm setting up the exact same formula (syntax, column name matches), but it's not working (Invalid Ref). Did you establish any links prior to the video? Thanks again!

Ryan Sides

At 0:50 you'll see the cross sheet refs I have setup already.

One for Name, Email and Phone

SNickNBCUniUSH

@Ryan Sides interesting - do you see anything wrong with the syntax by chance? Screenshot attached

SS.png

Ryan Sides

Nope. Can you post a screenshot of your Manage Resources screen?

SNickNBCUniUSH

@Ryan Sides Sure. See below. I created a reference from the master sheet for each column.

Ryan Sides

Got it. So you'll need to change the words in your { } to match exactly to what you have in the Name column in the References list there.

SNickNBCUniUSH

@Ryan Sides both sheets use "Contact Name" for name - the master sheet and project sheet have all the same column headers. Or do you mean within the reference itself? If so, where within the reference? The actual name of the reference? Thanks for all the help!

Ryan Sides

It looks like your References are created, but they aren't pointing to your other sheet. If you go back to the video, I show how to create the reference by pointing to the other sheet. Once that's fixed...

Your words in { } need to match exactly to what you have listed in the Name field. You're telling the formula, "hey, go look here at this other sheet to get this information. I told which other sheet and which column from that other sheet when I created the cross sheet reference".

If this isn't working, that's ok. We can always jump on a zoom call and I can walk you through it. I'm happy to help.

SNickNBCUniUSH

@Ryan Sides - that would be great! I renamed all references to be the same as column headers and still stumped. ShaneMNickerson@gmail.com

Ryan Sides

Email sent

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.