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!
Answers
-
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
-
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!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!