Using sheet reference to build placeholder text for an email alert

I am attempting to build placeholder text for an email alert but I want to do it on another sheet referencing the data on my master sheet. It will include text and cell references. Basically I'm trying to write something like the following.

"Hi name, Cardholder has made a purchase at Vendor for $XX.XX. Please review and approve or decline the transaction"

(There will be a lot more but I figure if I can understand the way to start this, I can finish the formula)

I can't really use Index/Match because this will generate for each row added to the master smartsheet and VLookup also doesn't work. I have tried Collect and Join to no avail as well.

{Transaction} is the range of the master sheet.

Basically here is what I have so far.

="Hi Brian," + CHAR(10) + {Transaction} [Cardholder_First]@row + " " + {Transaction} [Cardholder_Last]@row + " " + "has made a purchase at" + " " + {Transaction} [Vendor]@row + "." + CHAR(10) + "Please Review and approve or decline the transaction"

I get an UNPARSABLE error.

It works on the master sheet but not on a separate sheet.

Find more posts tagged with

Formulas

References

#UNPARSEABLE

Accepted answers

All comments

Jason Tarpinian

You will have to INDEX/MATCH each of your {Transaction} ranges to make it work as you're describing above.

But is there a reason you're trying to concatenate it all into a single cell? In your automation you can just type it out as below and it should work just fine:

Hi Brian,

{{Cardholder_First}} {{Cardholder_Last}} has made a purchase at {{Vendor}}. Please Review and approve or decline the transaction

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.