Formula no longer works, please help. Thanks.

willim101

I want to create a formula that will allow cells from one sheet/form to auto-populate into another sheet/form. This worked a week or so ago, but something has went wrong. The goal here is to create ease of use for my team, to lessen the clicks and entries. There is a 2part questionnaire/form for requests they send in. For the fields they have completed on the first form, I would like them to be prefilled on the 2nd form.

Please help! I'm not sure what I've done wrong. Here is the formula:

="https://app.smartsheet.com/b/form/8e2?FileName="+ [Updated_Title]@row + "&Author_AssetOwner=" + [Author_AssetOwner]@row + "&Requestor_Email=" + [Requestor_Email]@row + "&Business_Unit=" + Business_Unit@row + "&Docl#=" + [Updated_UCNum]@row + "&Geography=" + [Geography]@row + "&MarCom_Collaboration=" + [MarCom_Collaboration]@row

The full name of the form was removed.

willim101

Not sure how the mention in the formula appeared, but, that's not included in my formula.

Paul Newcome

I see you are not accommodating any special characters. If there are any special characters or spaces within the string that is being output by the formula, the URL will not work.

Take a look through here and let me know if you need help rewriting the formula:

https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

willim101

Thanks Paul. I want the data entered in one form to populate in another. The instructions you provided are steps on how to prefill default values. The values will change depending on the data entered on the form. Does that make sense?

Paul Newcome

I understand what you are doing. The portion of the article I linked that I am referring to is down towards the bottom in regards to special characters that could be in those variable fields. Things like spaces will break a URL and need to be accommodated within the formula that outputs the URL string.

For example.

="URL?FieldName=" + [Field Name]@row

If there is a space in the dynamic data within [Field Name]@row, the existing formula would output

"URL?FieldName=text text"

What you need is a %20 in place of the space to output

"URL?FieldName=text%20text"

To do this you would use a SUBSTITUTE function.

="URL? FieldName=" + SUBSTITUTE([Field Name]@row, " ", "%20")

The list at the bottom of the link I provided gives some of the more common special characters that need to be "percent encoded" to generate a valid URL. In your use case, I see you have an email address field. In that case you would need to substitute "@" with "%40" for sure. Other fields may have other percent encodings that need accommodated.

There are a number of other posts here throughout the Community that provide additional information on Form URLs as well as additional percent encodings for other less common special characters.

willim101

I appreciate your time Paul. There are no spaces in the information that will be returned. None of the column headers, etc., contain spaces. This formula literally worked a week ago. I'm not sure what happened. I am using underscore (_) and period (.) n the place of spaces. :(

Paul Newcome

It isn't just spaces though. As I mentioned in my last comment, there are a number of special characters that need to be percent encoded to include the "@" being replaced with "%40" which is a safe assumption due to the fact that you are collecting an email address within your string.