#UNPARSEABLE error

Daniel Sova

Hi everyone. New to smartsheets and im having issues with my formula. Works fine on google sheets but dont know how to get it working on smartsheet. Please see an example:

="https://form.jotform.co/TechForceAdmin/job-closure-form-ll-hfc?locId="&B49&"&assetId="&D49&"&roadNumber82="&E49&"&roadNumber84="&F49&"&roadName="&G49&"&roadType="&H49&"&roadSuffix="&I49&"&localitysuburb="&J49&"&serviceabilityStatus="&L49&"&proposedLeadin91="&M49&"&status="&N49&"&accessDate="&P49&"&subcontractorCompany="&Q49

I know the cell references need to be changed from e.g. B49 to the name of the cell in smartsheet, but cant figure out the correct format. Your help would be much appreciated!

JamesR

For Cell References use either:

[Column Name]###    (when there are spaces in the name)

or

ColumnName###      (when there are no spaces in the name)

Note: if you get in the habit of always using Square Brackets [ ] it covers both options.

Replace your Concatenation Symbol (&) with the Pluss(+) sign

You can also use the SUBSTITUTE() Function

Robert S.

Hello Daniel,

 

Thanks for the question. James is correct on how to reference cells in Smartsheet. The square brackets [ ] are needed around the column name if the column name has spaces as mentioned, however they're also needed if the column name contains numbers or special characters. To learn more about all the ways to reference in Smartsheet, check out the help center article here (https://help.smartsheet.com/articles/2476171#reference).

 

Concatenating data is done with the plus symbol + instead of the ampersand &, however if I understand your use of this formula correctly you won't be able to use the SUBSTITUTE function or the find and replace feature. From what I can tell, you're using this formula to create a hyperlink and some of the ampersands are needed. Using these features will replace all of the ampersands with plus signs. In this case it would have to be manually changed. Here's an example of how this would look:

 

="https://form.jotform.co/TechForceAdmin/job-closure-form-ll-hfc?locId=" + B49 + "&assetId=" + D49 + "&roadNumber82=" + E49 + "&roadNumber84=" + F49 + "&roadName=" + G49 + "&roadType=" + H49 + "&roadSuffix=" + I49 + "&localitysuburb=" + J49 + "&serviceabilityStatus=" + L49 + "&proposedLeadin91=" + M49 + "&status=" + N49 + "&accessDate=" + P49 + "&subcontractorCompany=" + Q49

 

As you mentioned, you will need to update the formula to have the correct column names, keeping in mind the square brackets.

Julio A

https://community.smartsheet.com/discussion/comment/52416#Comment_52416

Thanks!