Welcome!

#UNPARSEABLE error

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!

Find more posts tagged with

Formulas

Comments

JamesR

For Cell References use either:

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

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Formula Not working adding a 0
Hello I could use some help with this formula. Here is the formula I am using the bright yellow column to hold the formula. so you can see the results of that formula below =IF(ISBLANK([Initial Approval Date]@row ), " ", [Homeowner Approval Initial Status intake]@row + [Homeowner Approval Initial Status Intake 2]@row +…
Date/ Time cutoff tracking
Hi there, I've created a sheet to where my team is tracking information received. In one column, we are logging the date and time information came in (ie: November 21, 2025 8:30 AM). I would like to add a checkbox column, with a formula specifying that the box be checked if the logged time is AFTER 8:30 AM, and left…
Join-Collect from dropdown?
I have a column named Territory, where I am trying to write a formula to populate it when names are chosen from an adjacent drop-down list column called Employee Attendee. If more than one name is selected from the Employee column, I would like their respective territories to populate in the Territory field, separated by a…