Thread: Form Query

Hi there,

I’m wondering if anyone’s able to share some of their wisdom with me, I’m trying to do something for work and I think I have a vague idea of what I want but the guides online are either unhelpful or not specific enough to the scenario I’m in;

We have a spreadsheet used to log all training requests from sections in our company. At the moment it’s just a large spreadsheet with an autofilter which can then be used to quickly display all requests for subject X, or by department Y . Selecting blank course date can show everyone who is still awaiting training etc. Although I accept that there are MANY betters ways of doing this, i’m unfortunately restricted to doing it in this method.

When someone phones with a new training request, the member of the team who receives the call will open the spreadsheet, go to the first blank row and type in the details of the person calling. However this is presenting a problem with people typing their own variations of names, sections etc. (particularly where the dreaded apostrophe starts to come into play). Although it takes only a few seconds per week to have a quick tidy of duplicates it’s been made very clear to me that this is something we wish to avoid.

I’ve had a look at the possibility of having the first sheet of the spreadsheet being an entry form (I have literally 0 knowledge of forms in excel but consider myself savvy enough to be able to find the answer if I’m given a little bit of direction in laymens terms!) However, to avoid the duplication problem is where any way to create a form whereby certain fields are forced to be selected from a drop down menu of options that are either pre-entered or taken from what is already in the column?

I’m imagining a layout where sheet 1 is just a form where the fields listed below can be entered and then click “add” or something to that effect, and the data is put in the first free row beneath. This would then allow us to protect the spreadsheet proper so that we can’t have people interfering by entering erronous data.

For clarification, the areas that would be on the form (and are columns in the spreadsheet) are:

Name : Would like this to be free entry
Section : Would like this to be selected from a drop down list
Department : Would like this to be selected from a drop down list
Course Name : Would like this to be selected from a drop down list
Date requested : Free Entry
Course date : Free Entry
Comments: Free Entry

Is there a specific name for the scenario I would like to create? If so it may just be that I need to google something specific and read the guide on there, I’m by no means expecting a member of this site to waste their time typing out step by step instructions on what I need to do!

This has worked really well for me guys thanks, and I now have an excellent looking form and data sheet.

My only problem is this : We are using this form entry method because some users in the team were writing their own versions of course names or client names (For example, St. Harrison's Catholic Primary School had about 6 alternatives in the auto filter, some with apostrophe, some without, some with school, some without etc.)

When I protect the ranges in the data sheet so that they can't be tampered with, I am no longer able to click save in the form ( as obviously, i'm trying to save data into a protected range).

is there any way to allow data to be entered into a protected range if it is via a form? If possible I would just like to protect the data sheet against users manually clicking and typing in cells.

Any insight you can give would be greatly appreciated,

Andy