combine multiple names and credentials into one column

In my source sheet, I have 3 columns for Speaker 1 and 3 columns for Speaker 2 like so

I'm trying to combine them into one column because i need to generate a document from that.

I use the formula:

=[Speaker 1 First Name]@row + " " + [Speaker 1 Last Name]@row + ", " + [Speaker 1 Credentials]@row

and it works fine, but i need to be able to add a second speaker in the string but only if there is any data. if no data, i need to skip it.

However, ideally i need to generate two or three different documents because i need them to sign a video form release - is it possible to generate a document for each of them individually?

The release form has a fillable field for 'Your Name' - that's why i'm combining last and first names and credentials together.

thank you!

Find more posts tagged with

Accepted answers

All comments

Matt Johnson

Hi @OshaK

Here is the formula you are asking for: (I put a " / " in between the names to separate them.

=IF([Speaker 1 Last Name]@row <> "", [Speaker 1 First Name]@row + " " + [Speaker 1 Last Name]@row + ", " + [Speaker 1 Credentials]@row, "") + " / " + IF([Speaker 2 Last Name]@row <> "", [Speaker 2 First Name]@row + " " + [Speaker 2 Last Name]@row + ", " + [Speaker 2 Credentials]@row, "")

As for the document builder question, if you create a different form for each person then you probably won't need this formula; if I'm tracking you right.

You could create (2) separate fillable documents and call one 1st Release Form and 2nd Release Form. Then you could pick each of them from Generate Document menu option and the Saved Mappings. They would've been created from the same pdf but they would be different mappings, so (2) separate documents to create.

I hope that helps.

Matt

Devin Lee

If you want this format:

Bob Smith, MD & Jane Doe, Phd

Use this formula

=JOIN(COLLECT([Speaker 1 Last Name]@row:[Speaker 1 First Name]@row, [Speaker 1 Last Name]@row:[Speaker 1 First Name]@row, NOT(ISBLANK(@cell))), "  ") + IF([Speaker 1 Credentials]@row = "", "", ", " + [Speaker 1 Credentials]@row) + IF(JOIN([Speaker 2 Last Name]@row:[Speaker 2 Credentials]@row) = "", "", " & " + JOIN(COLLECT([Speaker 2 Last Name]@row:[Speaker 2 First Name]@row, [Speaker 2 Last Name]@row:[Speaker 2 First Name]@row, NOT(ISBLANK(@cell))), "  ") + IF([Speaker 2 Credentials]@row = "", "", ", " + [Speaker 2 Credentials]@row))

If you want this format:

Bob Smith, MD

Jane Doe, Phd

Use this formula and also enable text wrap

=JOIN(COLLECT([Speaker 1 Last Name]@row:[Speaker 1 First Name]@row, [Speaker 1 Last Name]@row:[Speaker 1 First Name]@row, NOT(ISBLANK(@cell))), "  ") + IF([Speaker 1 Credentials]@row = "", "", ", " + [Speaker 1 Credentials]@row) + IF(JOIN([Speaker 2 Last Name]@row:[Speaker 2 Credentials]@row) = "", "", CHAR(10) + JOIN(COLLECT([Speaker 2 Last Name]@row:[Speaker 2 First Name]@row, [Speaker 2 Last Name]@row:[Speaker 2 First Name]@row, NOT(ISBLANK(@cell))), "  ") + IF([Speaker 2 Credentials]@row = "", "", ", " + [Speaker 2 Credentials]@row))

You can use the document builder / generate documents (https://help.smartsheet.com/learning-track/smartsheet-advanced/document-builder) in order to create the forms you need. You might need to create two versions if you need to create a separate document for each person. In that case you might want a Speaker 1 and Speaker 2 column. Then you can either manually generate sets of documents or create an automation to trigger the generation. Something simple like a column with a checkbox trigger so instead of needing to manually generate each one you can just check a box. If there are a ton of names you might consider writing a formula in the box that checks to see if there is a first name.

Speaker 1

=JOIN(COLLECT([Speaker 1 Last Name]@row:[Speaker 1 First Name]@row, [Speaker 1 Last Name]@row:[Speaker 1 First Name]@row, NOT(ISBLANK(@cell))), "  ") + IF([Speaker 1 Credentials]@row = "", "", ", " + [Speaker 1 Credentials]@row)

Speaker 2

=JOIN(COLLECT([Speaker 2 Last Name]@row:[Speaker 2 First Name]@row, [Speaker 2 Last Name]@row:[Speaker 2 First Name]@row, NOT(ISBLANK(@cell))), "  ") + IF([Speaker 2 Credentials]@row = "", "", ", " + [Speaker 2 Credentials]@row)

OshaK

thank you @Devin Lee and @Matt Johnson! I haven't tried it yet but looks like i have to have 2 separate document regardless. Thank you!

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.