Using a Database to Autofill Cells

Hi Smartsheet Community!

I am trying to create a cell autofill for email and phone in one sheet using another sheet as a database. The issue I'm running into is how do you select between identical names with different email/phone. In the database sheet, I've created a duplicate checkbox column with a formula that will check a name if it's a duplicate on the sheet. In the second sheet where I want to autofill the email and phone, I've used an Index Collect formula to identify the duplicate and added conditional formatting to identify the duplicate on both sheets.

I've used an IFERROR, INDEX COLLECT formula extensively to pull the email & phone. It's working, which is exciting. I just can't figure out the duplicate name situation. I've reached out to the Smartsheet Help Desk and they suggested that I change the first name to add a middle initial. Is there another way??

Also, if you have ideas you can share with me about using a Database sheet to autofill email & phone in another sheet in a more elegant way, I'd love to learn more!

Thank you!

Patty

Find more posts tagged with

Formulas

Automations

Accepted answers

All comments

Bassam Khalil

Hi @patty.cooper@fox.com

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Or if you like to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will write the exact formula for you then you can copy it to your original sheet.

My Email for sharing : Bassam.k@mobilproject.it

Andrée Starå

Hi @patty.cooper@fox.com

I hope you're well and safe!

Do you have to match it against the name only? How is the name (or value to match against) added?

In a similar use case in a client solution recently, I joined multiple values together to make them unique, and in this case, because there were so many cross-sheet formulas and it was a very advanced solution, I ended up collected everything in the sheet and then parsed it out.

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@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

patty.cooper@fox.com

Thank you for reply, Andree and Bassam!

Here are the formulas that I am currently using:

Database sheet has the following columns: Row ID, Ref #, Duplicate Check, Last Name, First Name, Email, Phone Number. Columns are matched in the event sheet to auto-populate email & phone when requirements are met.

I have identified duplicates w/conditional formatting:

Duplicate Entry

F=IFERROR(INDEX(COLLECT({Dup Check}, {Last Name_V2 _NFL - TEST DATABASE}, [Last Name]@row, {First Name_V2 _NFL - TEST DATABASE}, [First Name]@row), 1), "?")

Row ID

F=IFERROR(INDEX(COLLECT({ROW ID_NFL - TEST DATABASE}, {Last Name_NFL - TEST DATABASE}, [Last Name]@row, {Phone Number_NFL - TEST DATABASE}, [Phone Number]@row), 1), "?")

Ref #

F=IFERROR(INDEX(COLLECT({Ref3_NFL - TEST DATABASE}, {Last Name_NFL - TEST DATABASE}, [Last Name]@row, {First Name - NFL - TEST DATABASE}, [First Name]@row), 1), "?")

F=IFERROR(INDEX(COLLECT({Email_NFL - TEST DATABASE}, {Last Name_NFL - TEST DATABASE}, [Last Name]@row, {Ref3_NFL - TEST DATABASE}, [Ref 3]@row), 1), "?")

Phone

F=IFERROR(INDEX(COLLECT({Phone Number_NFL - TEST DATABASE}, {Last Name_NFL - TEST DATABASE}, [Last Name]@row, {Email_NFL - TEST DATABASE}, Email@row), 1), "?")

The formulas are working for unique first & last name. It's not working for the exception - same first & last name different email/phone. I'm wondering if there's a better way to do this. I tried VLOOKUP and having the same issue. Any ideas??

Thanks for your help 😊

Patty

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.