Using Automatic Calculations or Picking from List Calculations

SUMMARY

Works for Windows does not have the ability to display a "pick from list" option. You can simulate this behavior using the CHOOSE function.

Type the available options in the database form as labels and assign each one of them a number from 0 to <number of options in list>, sequentially, where <number of options in list> is the number of options in your list. You will need two fields: one field in which to enter the number and the other field to calculate the text value to be presented.

A WORKING EXAMPLE

Problem Description

You have a company with five employees: You want the employees to fill out time cards, but only to include their names. You want the time cards to perform the following functions automatically:

• Insert the employee's salary
  
  
• Calculate a commission based on the salary, and a certain percentage that is possibly different for each employee.

Also, there are employees with similar names that might conflict (for example, Jan and Janet). You could use an IF statement, but this would be long.

Solution

1. Assign an Employee number to each employee. (The employee will enter this in the form to prevent discrepancies with names). Also, assign the salary and commission percentage. For example:

       NAME    Emp. Number    Salary      Commission Percentage
       --------------------------------------------------------
   
       Jan          0         $100                  .5
       Janet        1         $600                  .10
       David        2         $100                  .7
       Dave         3         $600                  .10
       Alan         4         $100                  .5
   					

2. Create the four fields below:

   Field 1: Employee Number
   Field 2: Employee Name
   Field 3: Salary
   Field 4: Commission

3. In Field 2 (Employee Name), enter the following function:

   =CHOOSE(Employee Number,"Jan","Janet","David","Dave","Alan")

4. In Field 3 (Salary), enter the salary for each employee.

   =CHOOSE(Employee Number, 100,600,100,600,100)

5. In Field 4 (Commission), enter the following function:

   =Salary*(CHOOSE(Employee Number,.5,.10,.7,.10,.5))+Salary

When an employee enters his or her number, the sheet will automatically provide the employee name, salary, and commission.