Excel can do this, right?

August 11, 2009 4:21 PM

Can I set up an Excel spreadsheet so the user is presented with a series of fields, like a form, instead of having to enter data directly into the cell? [I'm having a hard time even wording this question properly, so I haven't been able to find the answer anywhere. I turn to AskMe. Further explanation follows...]

I'm working on a project for a corporate client which requires collecting data on a regular basis from dozens of employees spread throughout the property. Many or most of these people do not work with Excel (or possibly even computers) on a regular basis, so I'm trying to simplify the process for them as much as possible. Ideally, I'd like the user to be presented with a series of prompts or labelled fields and an entry box for thier data. Hitting "enter" would take them to the next field. All the fields could be on one screen. There would only be 5 or 6 fields. I want to prevent the user from having to find the right cell to enter the data.

So, is there a macro, or some way to hide the underlying spreadsheet? Security is not an issue; the sheet doesn't have to be hackproof, just as idiot-proof as I can reasonably do it.

Possibly pertient details: I'm working with Excel 2003. I can't upgrade to a newer version, and my ability to add software is limited. (Think Big Oil corporate network on-site at a refinery where security is high and the IT department is very strict.) Thanks MeFites!

posted by The Deej to computers & internet (14 comments total)

I know you said you have difficulty adding new software...but, it sounds like what you need is exactly what MS Access does. Probably a way excel can do it too (defer to the excel experts), but it's pretty easy in Access.
posted by melissasaurus at 4:24 PM on August 11

This is, of course, what databases are made for, but presumably you cannot use those. That is a shame, because it would be remarkably more convenient for you.

What you need is a worksheet with all the dropdown lists you will need. So maybe A1-12 are the months, and B1-7 are the days of the week, etc. Probably you need to name all these ranges.

Now make a worksheet that looks like the form you imagine. You will now create dropdown lists. Eg, A1 says Month:, and B1 is the list from your other worksheet. You need to look to Excel 2003 help for how to set up a dropdown list in that program. Make sure you've used background colours and boxes to make it look like a form.

Hide all your unused rows and columns. Also hide the worksheet with the data, and delete any unused worksheets.

Now you need to protect the form worksheet, and unprotect only the dropdown boxes. Again, the help file will tell you exactly how to do it. (I am sorry I don't have access to 2003.)
posted by jeather at 4:35 PM on August 11

Since I didn't bother to google it for some reason:
Creating drop-down lists in Excel 2003
Protecting worksheets in Excel 2003

I can send you a spreadsheet with some sample dropdown lists if you would like (created in a higher version of excel but saved as 2003 version).
posted by jeather at 4:40 PM on August 11

As others said, what you really need is a database. Having said that, however, this is how you can accomplish it in Excel, or at least how I would. Ironically, my suggestion comes directly from an Excel product used by oil terminal storage facilities to track inventory levels.

The main steps are:
1. Unlock all the cells that you want them to enter data into. You do this by going to the cell's formatting properties, then to the Protection tab and un-checking the appropriate box.
2. Color-code your cells so that it's visually obvious which ones are for data-entry. I suggest hiding the grid in the workbook properties, and then drawing borders around the data-entry cells.
3. Protect the worksheet (somewhere under the Tools menu. No idea where it is in The Ribbon). Use a password to prevent people from un-protecting it accidentally.

Now, you should be able to press Tab and be guided through the unprotected data-entry cells one at a time. Each press of tab should skip over all the protected cells and take you to the next unprotected one.

Once you accomplish the above, you can write a macro to take the data from these cells and use it where it needs to be used. This macro would be associated to a button placed on the worksheet with a name like "Submit Data" and it would post to a hidden sheet, or to another workbook, or to a database via ADODB (which is what we did).
posted by odinsdream at 4:43 PM on August 11

Great info so far everyone... Thanks tons. I'm away until later this evening, but I'll check back in.
posted by The Deej at 4:49 PM on August 11

A database would be a better option, but sometimes you can't control everything.

Here is a sample Excel file with a macro that will save the information to the "Data" tab. B4:B5 pull their drop down data from the "Template" tab. The macro searches the "Data" tab looking for the first open row in A. Then copies the data from Sheet1 to the open row.

This should get you started.
posted by Climber at 5:46 PM on August 11

You might also want to look into Google Documents - forms creation. Pretty easy to use.
posted by mazienh at 6:27 PM on August 11

Google Documents forms are fabulous. I used them to get about 10 different people to enter data. Then when we were done, I exported it to excel and was good to go.
posted by rvrlvr at 6:33 PM on August 11

I came here to say Google Documents-Forms, but I realize now that I am 3rding it. If they have access to the web, this would be a big easy win.
posted by milqman at 7:42 PM on August 11

We kind of have something like this at work. Basically, the guys who created it just shaded the cells they didn't want people to use gray; the only cells that are white are the ones where users are supposed to enter data. Also, they turned off "show grid lines" and selectively used borders to make it look more like a form. But these are cosmetic workarounds.

Another thing that might be useful, depending on how you use the data gathered, is to create multiple tabs. Data gets entered in one, calculations on another. But if you are just importing this straight in to a database, that doesn't really apply to you. Instead, you could use tabs to group related data. Tab 1 is where you enter info about the customer, Tab 2 is where you enter info about the sale, and so on.
posted by ifandonlyif at 7:58 PM on August 11

Climber, thanks for putting that together (and thanks, jeather for your info and offer!) That may well be the best solution. It's pretty close to what I have now, but using drop-down lists is probably ideal, because the entries will be only a numerical value from a limited range.

And, thanks again everyone. There's tons of great info here that will come in handy. I'll dig into some hands-on work tomorrow and see if I can piece something together.
posted by The Deej at 8:49 PM on August 11

You know, I've been playing with Acrobat recently, and it has the ability to create forms, mail them, and then keep track of the responses.

Whereas access forms are pretty bulletproof, acrobat forms are tremendously bulletproof and pretty much look just like a sheet of paper with spaces to type. You can also simply scan in existing forms and the automated form-maker is pretty good at figuring out where you want them to type. (And then of course you tweak it from there.)

But really if they're super technically unsavvy, I'd make an access db, hand them paper forms, and then manually enter the info.
posted by TomMelee at 4:59 AM on August 12

Thanks TomMelee! I've done a lot of work with Acrobat forms, and they are indeed great. But, in this case I have to end up with an Excel spreadsheet, because the data then has to be grabbed by yet another program to be turned into an interactive Flash interface on a website.

I'm trying to make it as self-sustaining as possible, since they don't want to pay my consulting fees indefinitely, yet this project will require monthly data input for the foreseeable future.
posted by The Deej at 6:15 AM on August 12

It seems I'm 4thing Google Docs.

It has the advantage that the form can be accessed, and filled-in, by an iPhone or other mobile device with internet access.
posted by James Scott-Brown at 2:51 PM on August 12