is it possible to create a search form to query existing data in a sheet?

Steve Cohen

I have a smartsheet with customer data and I don't want the users accessing the sheet directly. Is it possible to create a form where the user enters a customer id number and the form populates with the customer information from the smartsheet row containing that information.

Kelly Moore

Hey @Steve Cohen

A form cannot do lookups directly, using a form you would need to couple this with a mechanism (a separate sheet, dashboard, update request, report, etc) that could return the information to the user.

My work around used a published sheet that was nothing more than a 'lookup page'. I have fields designated for data entry and the result field (Form Data) immediately returns the desired value. I published the sheet to ensure it was open to anyone in the organization. No data is retained on the lookup sheet - there is no need for the user to save the sheet and if they do, I overwrite their info using Change Cell Value/Clear Cell Value. Because of this, only one row is ever used on the sheet. The sheet always opens exactly as shown below with the yellow row being the input row.

cheers

AnkaPG

@Kelly Moore Could you share the formulas you used?

Kelly Moore

Hey @AnkaPG

In the screenshot above, the blurred column is 'Name'. There were also 7 or 8 hidden fields in the sheet that I had doing lookups, based on the 3 entries that the submitter made. Because I was able to push this info into the form, it saved having to do these lookup calculations on an already very large sheet that was approaching the max number of calculations permitted.

Remember when pushing info into a form that if your form field is using a label, rather than a column name, you must use the label text in the query. Also, spaces must be accounted for in this kind of a query so inputting free text can be challenging. All of my submitter responses were from dropdown choices so I could standardize my form input.

Things to note:

• There should be a question mark that follows the form url. You can see it before my first field Name.
• Notice that the url starts with quotes and includes the first field name and the equal sign
• Note the ampersand that proceeds the form question/field name. Also note its enclosed in quotes
• Spaces and other special characters must be accounted for. A handy list can be found here

Let me know if you have more questions

Kelly