Error in Transposing formula from Google Sheets to Smartsheet

Emily Giordano

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!

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!!