1 Excel Forms A data form provides a convenient way to enter or

Excel Forms

A data form provides a convenient way to enter or display one complete row of information in a

range or table without scrolling horizontally. You may find that using a data form can make data

entry easier than moving from column to column when you have more columns of data than can

be viewed on the screen. Use a data form when a simple form of text boxes that list the column

headings as labels is sufficient and you

don't need sophisticated or custom form

features, such as a list box or spin button.

Excel can automatically generate a built-in

data form for your range or table. The data

form displays all column headers as labels

in a single dialog box. Each label has an

adjacent blank text box in which you can

enter data for each column, up to a

maximum of 32 columns. In a data form,

you can enter new rows, find rows by

navigating, or (based on cell contents)

update rows and delete rows . If a cell

contains a, the formula result is displayed in

the data form, but you cannot change the

formula by using the data form

To add a data form to excel - you need to have the insert form table active - to make it active and

add it to excel do the following:

Add the Form button to the Quick Access Toolbar

1. Click the arrow next to the Quick Access Toolbar, and then click More Commands.

2. In the Choose commands from box, click All Commands.

In the list box, select the Form button click Add.

Tip For more information about how to use the data form, see Add, edit, find, and delete rows by

using a data form.

Add a form to a blank worksheet:

Type the following headings across the tops of a blank spreadsheet -

Lake, Station, Number, Date, Time, Wind Direction, Wind Speed, % Cloud Cover, Air temp,

Secchi depth

Use the form table using these headings to easily insert data via a vertical from

Enter one line of data to identify one station on this sheet.

Go back to the headings - leave a blank column

Type the additional following headings:

Depth, Temperature, DO, % DO, Conductivity, Light

Put your in the blank space immediately under the depth heading and add another form:

Enter one row of data

What advantages / disadvantages do you see using these automatically generated form

tables?

Data Validation (making sure you get what you think you are getting)

You use data validation to control the type of data or the values that users enter into a cell. For

example, you may want to restrict data entry to a certain range of dates, limit choices by using a

list, or make sure that only positive whole numbers are entered. (Type in validation into the

search box in excel - then select the article to move through all details - below is the short

version with an example - NOTE:

Input messages and error alerts appear only when data is typed directly into the cells. They do

not appear under the following conditions:

A user enters data in the cell by copying or filling.

A formula in the cell calculates a result that is not valid.

A macro enters invalid data in the cell.

To restrict the type of data that is entered in the lake name for example - you want text only and

characters that are at least greater than 2 in length - and you may also consider restricting the

length

Do the following:

Put your cursor in the first blank cell under the Lake name heading

Go to the <Data> tab

Click <Data Validation>

A box will come up - fill in the various tabs as follows:

1 - on the settings tab - under allow - select <custom>

2 - uncheck ignore blank cells next to it

3 - in the formula field type =ISTEXT(A2>1) - this checks to see if the stuff

entered is actually text and two characters or more

4 - select the input message tab - here you give help when the user mouses over

the input field - in the body write Enter Lake Name

5 - select the Error Alert tab - here you can select a series of warnings to the user -

or can actually STOP the input of incorrect data - select Stop - then input

the message to display to the user to correct the problem e.g., in Title field

enter Lake Name in message field enter - Enter name, should be greater

than 2

Make sure for your validation you leave your overall column formatting set to ‘General’

Test your cell to ensure the validation works.

Go back and generate another Table form with which to input data in a column

Try incorrect data for field.

Naming variables

A name is a meaningful shorthand that makes it easier to understand the purpose of a cell

reference constant, formula, or table, each of which may be difficult to comprehend at first

glance. The following information shows common examples of names and how they can improve

clarity and understanding.

Example Type Example with no name Example with a name

Reference =SUM(C20:C30) =SUM(FirstQuarterSales)

Constant =PRODUCT(A5,8.3) =PRODUCT(Price,WASalesTax)

Formula =SUM(VLOOKUP(A1,B1:F20,5,FALSE), -G5) =SUM(Inventory_Level,-Order_Amt)

Table C4:G36 =TopSales06

See “Define and use names in formulas” in F1 Microsoft help for further explanations.

It is much easier to do calculations by defining variables, e.g., naming the temperature variable

Temp - rather than trying to get $C$20 right in every formula. Open the previous spreadsheet

(the one from the oxygen workshop) and see all the named variables in it.

Use the ‘name manager’ under the Formula tab to explore the various formulae and their names.

In a blank spreadsheet - set some variables, define them, then perform some calculations so you

see how easy and clear it is just to use the variable names rather than cell addresses.

Locking Spreadsheets but allowing input to certain cells

There are cases in which you want to restrict user input (maybe you once you’ve been

away from the spreadsheet for a while) so they don’t mess up carefully constructed formulae and

calculations - which you may not remember exactly how you set them up - so rather than be able

to wander all over the sheet corrupting it with new input - restrict input to certain areas by

locking the sheet. If you need input in certain areas you need to have those available.

BIG NOTE - once you lock and excel sheet with a password, and you forget the password - you

are pooched - these are unrecoverable. So I always use a second sheet for documentation - and

put the password right in the documentation - it will prevent you from corrupting data - but allow

you to unlock it once you figure out for what you need to unlock it.

So on a blank sheet somewhere - color a couple of cells which you wish to have open for user

input.

Highlight the block and right click it - go to <Format cells> and the protection tab - uncheck

locked - Ok out

Go to Review tab - click Protect sheet - enter optional password (see big note above)

Try entering data outside of your block and inside your block.

To set other blocks in which you can enter data - you need to unlock the sheet - then uncheck the

locked for those cells, then protect the sheet again.

More of getting what you want

Sometimes you need to limit the selection of the user in various ways - and only give them a

limited input choice. Check out the selection of the air pressure units on the oxygen spreadsheet

from the last module. This was done via the data validation as well - although there are several

ways to get this done.

Let’s say there are several formats in which you may obtain oxygen readings - mg/l, ppm, ug/L,

ml/L or ug/L

Make a Column (starting in N1) labeled Dissolved Oxygen and in it list all of the above

Define the column as DO using the label variables you just learned

Supposed in A2 you are interested in determining which DO will be entered - which will

determine how you tackle the DO value in any subsequent formulae

Put cursor in A2

Go to Data - and validation - this time choose <List> - uncheck ignore blanks

Enter =DO in the source (you are not telling excel to go get the DO variables you just defined

above)

Enter your desired input and error messages - test your validation to make sure you can only pick

one from the list.

In B2 - set a validation to only accept numbers equal to or greater than zero

How does this help you in figuring out what to do next?

Suppose in your calculations you need to have all your values in mg/L - you can now use the

wonderful vlookup feature - in which you search a table for the entry in one column and use the

corresponding value for it form another column (See: Look up values in a list of data - F1 Help in

Excel).

Complete your DO table by entering the conversions in the next column to the DO units (this is

the initial leg work you need to do to set these up) - go back to the table of DO and in the next

column enter “to get mg/L” then enter the following values in the rows 1,1,1000, .625, 625.

Then label all of the DO units and conversion numbers as DO_conversion_table - sort them in

ascending order based on the first column (this is crucial if you want lookup to work correctly).

Label column C as the Converted mg/L column

In C2 enter conversion and lookup formula to get the correct conversion value using the DO

units indicated by the user in A2.

Your entry should look something like: =B2/vlookup(A2,DO_conversion_table,2) - where the

syntax for vlookup is as follow:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value - value to lookup in first column (must be sorted in ascending order in table)

Table_array - is the table in which to lookup values

Col_index_num - is the column with corresponding value (1 is first column, 2 is second, etc.)

range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an

exact match or an approximate match:

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned.

If an exact match is not found, the next largest value that is less than lookup_value is

returned.

Important If range_lookup is either TRUE or is omitted, the values in the first column of

table_array must be placed in ascending sort order; otherwise, VLOOKUP might not

return the correct value.

I f range_lookup is FALSE, the values in the first column of table_array do not need to be

sorted.

If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If

there are two or more values in the first column of table_array that match the

lookup_value, the first value found is used. If an exact match is not found, the error value

#N/A is returned.

Inserting Controls (scroll bars, radio buttons, etc.) Another way to control input to certain

functions and values

Insert scroll bars or spin bars - these will take the form of sliders or increase/decrease arrows and

can be very handy if you want to lock input to models or functions you are attempting to model -

or provide a handy visual input device

They are found under the Developer tab (this will show up next to the view tab) but in most cases

you need to make it active first

Got to the Excel button (top left) - click it, then in the bottom right of the frame, click Excel

options - then popular - then check the checkbox next to show Developer in ribbon - ok out

On ribbon then click developer tab - click insert in the controls box - you have two choices -

form controls or activeX - the forms will create and run a macro in the workbook in which you

insert them - so you will need to save the workbook as an .xmls file (beware those that have

firewalls may prevent this type of file from being sent/received - because it is identified as a

macro containing worksheet - and we all know this is an all to favorite way to send computer

viruses)

Click on the desired input control and drop into spreadsheet - I recommend the macro ones for

now rather than the activeX. Put in - fix up, then customize and set limits.