Error in Transposing formula from Google Sheets to Smartsheet

I recently revamped my new team's process from Google Sheets to Smartsheets and everything was peachy keen until I tried to transpose the last, painful formula. The purpose of this column is for my staff to have a quick & easy way to copy the relevant details of the row from one cell to a Jira ticket (my company does not allow the Jira Connector).

Can someone please look this over and let me know why I'm getting a syntax and/or #paresable error when trying to make this a column formula? As you can imagine, just the act of drafting this formula before attempting it was painful enough...lol.

=if([Request Type]@row="New Alarm","User: "&[Requestor]@row&char(10) &"Priority: "&[Urgency]@row&char(10) &"LOB: "&[LOB]@row&char(10) &"Channel: "&[Channel]@row&char(10) &"Request: "&[Request Type]@row&char(10) &"Problem Statement: "&[Problem Description]@row&char(10)&"Alarm Name: "&[Alarm Name]@row&char(10) &"Alarm Description: "&[Proposed Alarm Description]@row&char(10) &"Alarm Type: "&[Alarm Type]@row&char(10) &"Projected HR: "&[Projected HR]@row&char(10) &"Projected Vol: "&[Projected Volume]@row&char(10) & "INTAKE#: "&[SS ID]@row,

if([Request Type]@row="Modify Alarm","User: "&[Requestor]@row&char(10) &"Priority: "&[Urgency]@row&char(10) &"LOB: "&[LOB]@row&char(10) &"Channel: "&[Channel]@row&char(10) &"Request: "&[Request Type]@row&char(10) &"Problem Statement: "&[Problem Description]@row&char(10) &"Alarm Name: "&[Alarm Name]@row&char(10) &"Alarm Description: "&[Current Alarm Description]@row&char(10) &"Alarm Type: "&[Alarm Type]@row&char(10) &"Current HR: "&[Current HR]@row&char(10) &"Current Vol: "&[Current Volume]@row&char(10) &"Projected HR: "&[Projected HR]@row&char(10) &"Projected Vol: "&[Projected Volume]@row&char(10) & "INTAKE#: "&[SS ID]@row,

if([Request Type]@row="Analysis (General)","User: "&[Requestor]@row&char(10) &"Priority: "&[Urgency]@row&char(10) &"LOB: "&[LOB]@row&char(10) &"Channel: "&[Channel]@row&char(10) &"Request: "&[Request Type]@row&char(10) &"Problem Statement: "&[Problem Description]@row&char(10) & "INTAKE#: "&[SS ID]@row,

if([Request Type]@row="Analysis (Alarm)","User: "&[Requestor]@row&char(10) &"Priority: "&[Urgency]@row&char(10) &"LOB: "&[LOB]@row&char(10) &"Channel: "&[Channel]@row&char(10) &"Request: "&[Request Type]@row&char(10) &"Problem Statement: "&[Problem Description]@row&char(10) &"Alarm Name: "&[Alarm Name]@row&char(10) &"Alarm Description: "&[Current Alarm Description]@row&char(10) &"Alarm Type: "&[Alarm Type]@row&char(10) &"Current HR: "&[Current HR]@row&char(10) &"Current Vol: "&[Current Volume]@row&char(10) & "INTAKE#: "&[SS ID]@row,

if([Request Type]@row="Reactivate Alarm","User: "&[Requestor]@row&char(10) &"Priority: "&[Urgency]@row&char(10) &"LOB: "&[LOB]@row&char(10) &"Channel: "&[Channel]@row&char(10) &"Request: "&[Request Type]@row&char(10) &"Problem Statement: "&[Problem Description]@row&char(10) &"Alarm Name: "&[Alarm Name]@row&char(10) &"Alarm Description: "&[Current Alarm Description]@row&char(10) &"Alarm Type: "&[Alarm Type]@row&char(10) &"Projected HR: "&[Projected HR]@row&char(10) &"Projected Vol: "&[Projected Volume]@row&char(10) & "INTAKE#: "&[SS ID]@row,

if([Request Type]@row="Retire Alarm","User: "&[Requestor]@row&char(10) &"Priority: "&[Urgency]@row&char(10) &"LOB: "&[LOB]@row&char(10) &"Channel: "&[Channel]@row&char(10) &"Request: "&[Request Type]@row&char(10) &"Problem Statement: "&[Problem Description]@row&char(10) &"Alarm Name: "&[Alarm Name]@row&char(10) &"Alarm Description: "&[Current Alarm Description]@row&char(10) &"Alarm Type: "&[Alarm Type]@row&char(10) &"Current HR: "&[Current HR]@row&char(10) &"Current Vol: "&[Current Volume]@row&char(10) & "INTAKE#: "&[SS ID]@row,

"User: "&[Requestor]@row&char(10) &"Priority: "&[Urgency]@row&char(10) &"LOB: "&[LOB]@row&char(10) &"Channel: "&[Channel]@row&char(10) &"Request: "&[Request Type]@row&char(10) &"Problem Statement: "&[Problem Description]@row&char(10) &"Alarm Name: "&[Alarm Name]@row&char(10) &"Alarm Description: "&[Current Alarm Description]@row&char(10) &"Alarm Type: "&[Alarm Type]@row&char(10) &"Projected HR: "&[Projected HR]@row&char(10) &"Projected Vol: "&[Projected Volume]@row&char(10) & "INTAKE#: "&[SS ID]@row))))))

THANK YOU!

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @Emily Giordano

I think the error was caused by your ampersands.

=IF([Request Type]@row = "New Alarm", "User: " + Requestor@row + CHAR(10) + "Priority: " + Urgency@row + CHAR(10) + "LOB: " + LOB@row + CHAR(10) + "Channel: " + Channel@row + CHAR(10) + "Request: " + [Request Type]@row + CHAR(10) + "Problem Statement: " + [Problem Description]@row + CHAR(10) + "Alarm Name: " + [Alarm Name]@row + CHAR(10) + "Alarm Description: " + [Proposed Alarm Description]@row + CHAR(10) + "Alarm Type: " + [Alarm Type]@row + CHAR(10) + "Projected HR: " + [Projected HR]@row + CHAR(10) + "Projected Vol: " + [Projected Volume]@row + CHAR(10) + "INTAKE#: " + [SS ID]@row,

IF([Request Type]@row = "ModIify Alarm", "User: " + Requestor@row + CHAR(10) + "Priority: " + Urgency@row + CHAR(10) + "LOB: " + LOB@row + CHAR(10) + "Channel: " + Channel@row + CHAR(10) + "Request: " + [Request Type]@row + CHAR(10) + "Problem Statement: " + [Problem Description]@row + CHAR(10) + "Alarm Name: " + [Alarm Name]@row + CHAR(10) + "Alarm Description: " + [Current Alarm Description]@row + CHAR(10) + "Alarm Type: " + [Alarm Type]@row + CHAR(10) + "Current HR: " + [Current HR]@row + CHAR(10) + "Current Vol: " + [Current Volume]@row + CHAR(10) + "Projected HR: " + [Projected HR]@row + CHAR(10) + "Projected Vol: " + [Projected Volume]@row + CHAR(10) + "INTAKE#: " + [SS ID]@row,

IF([Request Type]@row = "Analysis (General)", "User: " + Requestor@row + CHAR(10) + "Priority: " + Urgency@row + CHAR(10) + "LOB: " + LOB@row + CHAR(10) + "Channel: " + Channel@row + CHAR(10) + "Request: " + [Request Type]@row + CHAR(10) + "Problem Statement: " + [Problem Description]@row + CHAR(10) + "INTAKE#: " + [SS ID]@row,

IF([Request Type]@row = "Analysis (Alarm)", "User: " + Requestor@row + CHAR(10) + "Priority: " + Urgency@row + CHAR(10) + "LOB: " + LOB@row + CHAR(10) + "Channel: " + Channel@row + CHAR(10) + "Request: " + [Request Type]@row + CHAR(10) + "Problem Statement: " + [Problem Description]@row + CHAR(10) + "Alarm Name: " + [Alarm Name]@row + CHAR(10) + "Alarm Description: " + [Current Alarm Description]@row + CHAR(10) + "Alarm Type: " + [Alarm Type]@row + CHAR(10) + "Current HR: " + [Current HR]@row + CHAR(10) + "Current Vol: " + [Current Volume]@row + CHAR(10) + "INTAKE#: " + [SS ID]@row,

IF([Request Type]@row="Reactivate Alarm","User: "+Requestor@row+CHAR(10) +"Priority: "+Urgency@row+CHAR(10) +"LOB: "+LOB@row+CHAR(10) +"Channel: "+Channel@row+CHAR(10) +"Request: "+[Request Type]@row+CHAR(10) +"Problem Statement: "+[Problem Description]@row+CHAR(10) +"Alarm Name: "+[Alarm Name]@row+CHAR(10) +"Alarm Description: "+[Current Alarm Description]@row+CHAR(10) +"Alarm Type: "+[Alarm Type]@row+CHAR(10) +"Projected HR: "+[Projected HR]@row+CHAR(10) +"Projected Vol: "+[Projected Volume]@row+CHAR(10) + "INTAKE#: "+[SS ID]@row,

IF([Request Type]@row="Retire Alarm","User: "+Requestor@row+CHAR(10) +"Priority: "+Urgency@row+CHAR(10) +"LOB: "+LOB@row+CHAR(10) +"Channel: "+Channel@row+CHAR(10) +"Request: "+[Request Type]@row+CHAR(10) +"Problem Statement: "+[Problem Description]@row+CHAR(10) +"Alarm Name: "+[Alarm Name]@row+CHAR(10) +"Alarm Description: "+[Current Alarm Description]@row+CHAR(10) +"Alarm Type: "+[Alarm Type]@row+CHAR(10) +"Current HR: "+[Current HR]@row+CHAR(10) +"Current Vol: "+[Current Volume]@row+CHAR(10) + "INTAKE#: "+[SS ID]@row,

"User: "+Requestor@row+CHAR(10) +"Priority: "+Urgency@row+CHAR(10) +"LOB: "+LOB@row+CHAR(10) +"Channel: "+Channel@row+CHAR(10) +"Request: "+[Request Type]@row+CHAR(10) +"Problem Statement: "+[Problem Description]@row+CHAR(10) +"Alarm Name: "+[Alarm Name]@row+CHAR(10) +"Alarm Description: "+[Current Alarm Description]@row+CHAR(10) +"Alarm Type: "+[Alarm Type]@row+CHAR(10) +"Projected HR: "+[Projected HR]@row+CHAR(10) +"Projected Vol: "+[Projected Volume]@row+CHAR(10) + "INTAKE#: "+[SS ID]@row))))))

Does this work for you?

Kelly

Emily Giordano

OMG @Kelly Moore you are an angel! Thank you SO much!

But I do have one follow-up question that I was not expecting - is there a way to copy the output of the formula vs the actual formula? I cannot figure out how to do that - I even tried pasting just the "values" or "plain text" and can't seem to get it to paste anything but the underlying formula...?

Thanks again!!

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.