Good morning, SF.

Preparing for an upcoming event, I'm trying to come up with a good way to gather potential customer information.

I'll have an iPad available (with no internet access). It's got Excel. I'm thinking there probably already exists a form or a template or such that would allow me to spin the iPad toward the user (potential customer) and present the user with a set of fields (name, address, email, etc.). The user fills in the fields and clicks a "save" button. Behind that, the information they entered gets stored to a second sheet in that workbook (or even to a separate workbook), and the form resets.

I used Excel a couple of weeks ago to create a form for some test data entry, but it's FAR from foolproof. I can't use something as finicky as that in next week's application. I need this to be sort of an input-only user interface -- can't have users "looking behind the curtain" and disturbing info collected from previous visitors, right?

Anyone built something like this already?
I'm surfing the web this morning and finding lots of "template builders" but so far getting nothing but frustration.

Thanks, all.

Excel based forms use VBA code in the background, it's a lot of work & a bit messy. VBA is going out of style quickly since it's a vulnerability.

MS Forms <> Sharepoint Lists <> PowerAutomate has basically replaced that aspect, but it is online-only.
Think outside the spreadsheet. All you need is a structured list, can be as easy as comma separated that can be imported into excel.

Jotform is 1 tool I've seen used (not used it personally) to do what you are asking.
I searched 'MS Forms Offline' and found this on the Jotform site. They even suggests other tools, so it's worth a read.
Article with some suggestions

This sounds like MS access would be a better fit than excel.
You can create a form and the each time the form is submitted, the info will get added into a database.

I'd be okay with using Access... just means I have to learn Access in about two days!

- - - -

ETA: My IT contact just told me that Access isn't available for iPad -- nor for MacBook. Dangit!
That really stinks, because it took about two minutes to find an Access database online that's just about perfect for this task.

Access/Excel forms are going to be really hard to use with an on-screen keyboard.
If you had internet available, MS Forms would be a 5-minute solution.

This is HTML territory, check out Jotform, I'd bet you can be up & running in less than 2 days. Hell, HR people can figure it out for Holiday Party sign-up/preferences, so it has to be easy.

Jotform... working on that now. Thank you!

I'm an expert in Excel and I understand databases like Access. As snidera said, what you're describing would require VBA.

But I have a work around for you in the short timeline that you have.

1. Create your "Form" on an Excel sheet. Put in the field names and where the spaces where you want the customer to enter. Use one cell for each value.
1a. You can make your sheet look pretty by (But do this after you've finished the form):
1a1. Hide gridlines: View > Show > Uncheck Gridlines, Formula Bar, Headings.
1a2. Hide Ribbon: Right most down arrow on ribbon > Full Screen mode. To bring back ribbon: Click 3 dots in top right corner> rightmost down arrow > always show ribbon.

1b. put borders around the cells where you want the customers to enter the value: Ctrl + 1 > Border > (you can figure out how to put in the borders).
1c. Change the fonts in the cells by Ctr + 1.

2. Protect the sheet but unprotect the data entry cells for the value. This prevents the person from entering data anywhere else or changing your form. You can speed 2a by selecting the input cells all together by holding Ctrl and clicking on each input cell.
2a. for each cell the curstomer enters their values: ctrl + 1 > Protection > Uncheck Locked.
2b. Protect the sheet: Review > Protect Sheet > 2b1. You don't need to put in a password.
2b2. keep checked: Protect worksheet and contents of locked cell. keep checked: Select unlocked cells. uncheck Select Locked cells.
2b3. OK.

3. Make copies of the form sheet:
3a. Right click on tab > move or copy > check create a copy > OK.
3b. Make many copies. after the first copy: Click on the tab of the copy > Shift > click on the original copy > repeat 3a. that will make 4.
3c. repeat 3b until you figure you have plenty of sheets.

4. Hide all sheets but one: click on the tab of the first sheet > shift > click on the second to last sheet > right click > Hide.

You turn the tablet to the customer, have them enter their info. they use the tab to go from field to field or maybe enter. when they're done, they hand it back to you and you:

1. right click on the sheet tab > unhide the next blank sheet.
2. right click on the just completed sheet > hide
3. and you're ready for the next customer.

When the show is done, you unhide all the sheets and take down the information for each customer or you can get someone to do the VBA programming to pull the information from each sheet into a consolidated sheet.

That's the fastest route to getting you what you want in time for the show.

Thank you, too, Rey. I'll work on that in parallel -- I've needed to learn about Excel's protection feature for a good while now.

Use Microsoft Forms in the Office Suite.

You can create all those fields and it will export an Excel file to you.

quote:

Originally posted by KevH:
Use Microsoft Forms in the Office Suite.

More to learn! Thank you.

I use Claude AI for these.

Ask what you want. Let AI serve.

State all assumptions and constraints. List requirements. Give specific instructions. I need a sign up form …

It is usually successful.

Thank you all for your help. Here's how it stacks up as of quitting time:

Excel on the iPad is somewhat different from Excel on the PC, and that's a bit limiting. I tried creating the form on PC and transferring it to the iPad. I could open the file, but the features ("Form," in particular, weren't available.) So I grabbed a MacBook off the shelf, thinking it'd be closer to PC in function. It is, but I still can't get the "Form" function to show up.
Project Apple: on hold.

Access, as we learned, won't run on Apple products without a bunch of gyrations (Parallels, for instance), and that won't come together as quickly as I need it to. That stinks, as I found a usable template very quickly, and was looking forward to learning more about Access.
Project Access: "Access denied."

Microsoft Forms: I did find the app and started searching existing templates to build on. I didn't get a lot of time with it this afternoon, so I have to label that effort as:
Simply incomplete.

Rey's Excel method: I like it! Just didn't get enough time to make a sample today. I've no idea how many contacts we expect to record, so making "plenty" will be either a) I didn't make enough, or b) I have to make more on the fly. Or c) we need more during the event, I'm off taking care of some other task, and none of my crew would be quick to dive into tinkering with the workbook. I still like it.
Project Rey: incomplete.

Snidera brought up Jotform. It was a bit daunting at first, but after I had spent a half-hour with it, I had a working form, plus integration to a worksheet in Google Sheets. I'm not a great fan of "The Big G" being woven into SO many things these days, but that's where we are right now. The advantage comes in the form of simplicity. When the event is over, I can copy off the G Sheet to Excel, hand it over to the Marketing group, shut down my brand new Google account, and walk away with good results and a smile.

I spent another half-hour banging around on Jotform, and learned that it'll make an app. I'm notoriously slow to load up a bunch of apps, but... hey, remember that iPad that started this whole thing? Let's pick that up! I was able to create the app, install it on the iPad, and have it running smoothly in about ten minutes. Jotform offers a downloadable QR code, too, so I can display that somewhere and then people can scan it, load the form, and submit it, all on their own devices. That's a capacity increase, in case we get super busy. Bonus!
Project Jotform: "Houston, we have a success!"

4MUL8R brought up Claude AI. I'll admit that as nerdy as I am, I have not yet delved into the AI world. A few people at work have jumped in; I simply haven't yet. It's on my list now, though.

As always, I thank you all for your help. I've learned several new things today, solved a problem, had a great experience with teh corporate IT rep, and set my team up for success next week. If I ever get the custom user title here, I'm going to ask for "Curious and grateful." More often that not, when there's something I need to learn about based on others' experience, I come to SIGforum. This place rocks.

Glad you got it.
Like I said above, MS Forms would have been the easiest/most direct way to tackle this problem, but you'd need internet access for it to work.

Excel "Forms" don't work on mobile or cloud due to VBA. That's why VBA is dying. Just another useless skill I've learned.... Maybe MS will add HTML/Java support, that will make even less secure

While a 'structured list' is technically a database, CSV still exists because you don't need database-complexity for a simple table/list.
MS forms is the most under-utilized MS Office product, IMO. It's relatively easy/quick/intuitive to create 'applications' using Forms>Sharepoint>PowerAutomate. PowerAutomate is the killer. It's goofy to work with, but so powerful when you get it right.
I made a request>response workflow very quickly & easily. If it wasn't for file attachments being really stupid to set up, it would have taken 30 minutes from start to finish. It takes the place of a paper/excel 'Form' that was emailed, then the output emailed back and turns it into a 4-Question online form, then looks up all the other info based on those 4 questions, notifies the work team & gives them a printable workorder (A demand I rolled my eyes & just did), then when they upload their response, it notifies the requestor. You have the time-to-complete and the actual output in a list form (filterable, exportable). I also added scheduling for repeated tasks (weekly / monthly).

quote:

Originally posted by vthoky:
Snidera brought up Jotform. It was a bit daunting at first, but after I had spent a half-hour with it, I had a working form, plus integration to a worksheet in Google Sheets. I'm not a great fan of "The Big G" being woven into SO many things these days, but that's where we are right now. The advantage comes in the form of simplicity. When the event is over, I can copy off the G Sheet to Excel, hand it over to the Marketing group, shut down my brand new Google account, and walk away with good results and a smile.

I spent another half-hour banging around on Jotform, and learned that it'll make an app. I'm notoriously slow to load up a bunch of apps, but... hey, remember that iPad that started this whole thing? Let's pick that up! I was able to create the app, install it on the iPad, and have it running smoothly in about ten minutes. Jotform offers a downloadable QR code, too, so I can display that somewhere and then people can scan it, load the form, and submit it, all on their own devices. That's a capacity increase, in case we get super busy. Bonus!
Project Jotform: "Houston, we have a success!"

Shouldn't have to do anything but send Marketing a link & smile. Once online, the data saved to the ipad should sync with anything entered elsewhere. Again, not used Jotform, but I'm sure you can just 'share' the list with whoever you want.

I'd be willing to bet you'll get more action if you display that QR code and/or create some business cards with it on there.

I'd like to tinker with Forms some more. Based on the way you describe it, I wonder if it would be helpful in "paperless-ing" our ECR/ECO system. We've got a method now, but it's a little clunky.

And yes, I can share a link to the G Sheet and walk away. That's a great feeling.
I did get the IT rep to get me online with the iPad, so now it updates the G Sheet as soon as a completed form is submitted. Jotform admits it's slightly delicate -- you don't want anybody changing column names or moving data around -- so I'll wait until after the event to give Marketing a link. "Make it stable, then hand it over," so to speak.

I've got a stack of business cards and a small label printer -- I can print a batch of QR code labels, stick them on the cards, and hand 'em out like candy at Halloween.

quote:

Originally posted by vthoky:


I did get the IT rep to get me online with the iPad, so now it updates the G Sheet as soon as a completed form is submitted.D

Actually, I'm not quite sure what your function is but since you do have IT resources, why not have them do it?

You want something to take in potential customer / contact information. Figure out what info you need /want. Get other department/ people's input as to what they want. Whittle it down to something manageable so people don't think they're applying for a mortgage loan or a proctology exam.

Figure out where you want that info to go to (do you have a customer contact database or Customer Relations System?

Figure out what outputs you want in terms of reports or analysis, then give it all to the IT function to solve. Do what you do best and let IT do what it does best.

My function is manufacturing engineering — learning the process, teaching the crew, then being the “production concierge.” My department’s main task is to provide the tools and techniques necessary for the crew to build the product.

That said… I have some IT resource available, but definitely not to the extent you describe. Need hardware? Need software? Call those guys. Beyond that… it’s “figure it out yourself.” They’re off-site, and some 3 hours away by car, so it’s not like they’re right down the hall for a quick meet-and-request.

There may be a Customer Relations System, but I’ve not been made aware of it. In short, my leader came to me and asked me to create a way (a form) by which we could easily gather on an iPad a lot of customer contacts at a trade show next week. So it was on me to figure several ways to gather the info, then rely on the IT bunch to help me out with the hardware and software necessary, if possible.

In my experience with this IT crew, it’s sort of like a buffet: there’s stuff there, but you have to know what you want and be willing to fetch it yourself. And yes, I’ll admit that was a horrible analogy.

But I now have a form that works on an iPad (as requested by my leader), a QR code that makes it so that the user can fill in the form on his own device (or ours), and a G Sheet that contains the necessary contact information for handing over to Marketing. It turned out well, I think, and will be useful for another event downtown this summer, likely without any changes at all. So now I can go back to planning a workbench-and-tooling layout for a new product coming on, and learning more about this CNC router.

The soccer Moms use Sign Up Genius to quickly sign up and organize events. I know it works well if you need to schedule shifts for a volunteer event.

I'm not a fan of the Googledom but my last shop before retirement was. I'm shocked nobody suggested Google Forms as it comes with templates like "Event Registration" & "RSVP" Even creating your own is quick & simple. It hurts way down deep in the feelers to see people talking about VBA getting sidelined. Did some fun projects with Access & VBA.

quote:

Originally posted by DetonicsMk6:
It hurts way down deep in the feelers to see people talking about VBA getting sidelined. Did some fun projects with Access & VBA.

Doing VBA was a good part of how I put food on my table. I had a master file that had a custom menu ribbon at top and various VBA subroutines to do generic things with custom inputs. For each new project, I just put the main script that called each subroutine in order and where it was supposed to do it. All people had to do click on the icons to get the reports they want or process the data.