If you're looking to put together a number of pre-populated form links, we wanted to give you a way to do this more easily with Microsoft Excel. We'll be using a formula to pull in pre-existing data from cells as variables in the URL. We'll combine all of this to let Excel automatically create a unique URL for each row of data.
Check out this article if you need a quick refresher on using Pre-Population with Formstack.
The formula below is what exists in cell D6. You can also see this in the formula bar in the below screenshot.
=$D$1&"?city="&A6&"&state="&B6&"&weather="&C6
Now, let's break down the formula piece by piece.
=$D$1&"?city="&A6&"&state="&B6&"&weather="&C6 =$D$1
This bit stars by referencing the URL of our form in cell D1. We want to lock this referenced form URL, so to do this, we use dollar signs in the formula. The dollar signs in "$D$1" lock both the column and row for the formula.
&"?city="&A6
Next, text within &""& is referenced literally in our Excel formula. The text we have within &""& is ?city=. This is then attached to the end of our form URL from the step above. Outside and after the &""&, we have the cell reference of A6. This cell reference will drop in the city we want to populate from the row. In the case of this URL in cell D6, it will populate with "Gary" as the city. As we drag the formula down, that cell reference will change since it is not locked.
&"&state="&B6
This bit works the same as the previous part of the formula. We're referencing literal text we want to place in the URL string as well as a cell with data. This cell, B6, contains the state we want to reference and populate. Notice here between variables we also separate them with an ampersand in the literal string.
&"&weather="&C6
Again, this bit works the same as the previous part of the formula. We're referencing literal text we want to place in the URL string as well as a cell with data. The cell C6 has the the weather input we want to populate.
https://subdomain.formstack.com/forms/hello?city=Gary&state=Indiana&weather=windy
The pieces all combine to give us an output of a finished pre-population URL that's specific to this row of data. Once you drag the formula down through your spreadsheet of data, the formula adapts to reference the proper cells. Your pre-populated URLs are then created!
The result is a link that populates the form data when visited.
Note: If you'd like to populate a Date field from Excel, the cells should be formatted as 'Text' fields and the date format should be the full month, day, and year (for standard date formats), ie. January 1, 2024.