Hello, I'm the video professor, and you are about to learn software by means of a creative
new concept in the computer industry.
I'll take you step by step through learning your new software package.
On the screen now are some other tapes available in my series.
With them, you can watch and learn at your own speed, stop and practice any time you
want and review topics you don't understand.
I suggest that you view each tape in its entirety, then go back through the lesson with your
computer and your software and practice each step.
All of my tapes are designed to get you up and running quickly.
Once you have viewed and practiced everything that is on these tapes, I think you'll find
your software manual much easier to understand.
And now, let's get started.
Welcome to Level 2 of Lotus 1-2-3 Release 2.2 and 3.0.
Even if you have very limited experience with 1-2-3, this lesson will be useful.
You should already know how to move the cell pointer, write simple formulas, save and retrieve
spreadsheets, print a spreadsheet, clearing the screen and using the command menu.
I would like to introduce you to Pam, our student helper for today.
Hello, Professor.
Hi, Pam.
If you haven't started 1-2-3 yet, go ahead and do it now.
To begin, you should have an empty Lotus screen with a cell pointer in A1.
We are going to create a new spreadsheet that tracks weekly expenses for a hypothetical
person.
There are several features of Lotus 1-2-3 that I will introduce to you while we create
this spreadsheet together.
Then we will create one more spreadsheet that will be used to teach you how to use 1-2-3
for database operations.
The following topics will be introduced in this learning lesson.
Absolute and relative cell addressing.
You will use this feature to make sure a formula always refers to one cell, even when it is
copied to other cells.
Range format.
You will use range format to change the appearance of cells to dollars and cents.
Range names.
You will use range names so that you won't have to remember cell address ranges when
you perform the file combine command.
File combine.
You will combine parts of two files into a third file.
Database operations.
You will use 1-2-3 to maintain a phone list for you.
You will then sort the records in the phone list, find a record, and have 1-2-3 extract
specific records from the phone list.
Headers, footers, and borders.
These three features allow you to enhance the printed version of a spreadsheet with
text, dates, and page numbers that appear at the top and or bottom of every printed
page.
The first thing we will need to do is type the column and row headings for the expense
report.
We prefer to type column and row headings in capital letters.
So turn the caps lock on by pressing the caps lock key.
You can tell when the caps lock feature is on by the word caps in the lower right corner
of the screen.
Let's type the word name followed by a colon in cell A1 and move the cell pointer over
to B1.
While we are in row 1, let's move over to column G and type the word weekly.
We can move the cell pointer down one row and work backwards if we want.
Type the word totals and move one cell to the left.
Now type the days of the week working backwards from cell F2.
Abbreviate the weekdays to three letters just for fun.
We should end up in column A.
Now let's type the information we need in row headings.
Follow along as we enter the row headings.
Lodging in A3.
Breakfast in A5.
Lunch in A6.
Dinner in A7.
Total meals in A9.
Ending mileage in A11.
Ending miles in A12.
Total mileage in A14.
And mileage dollars due in A15.
And total expenses in A17.
Your spreadsheet should look like this one does now.
If it doesn't, take all the time you need to copy the spreadsheet by pausing or rewinding
the tape.
Let's set the column's width of column A to 16 while we're here.
That should make it wide enough.
Let's go through the keystrokes together.
First press slash to display the main menu.
Then press W to select worksheet from the main menu.
Press C to select column from the worksheet menu.
And press S to select set width from the column menu.
Now 123 is prompting us to enter a column width.
Press the right arrow key until the highlight in the active cell is one character wider
than the longest heading in the column.
You can look up in the control panel to verify that the column width is 16.
And press the enter key to finish.
Now let's enter the lines that divide the subtotals and totals.
Move the cell pointer up to cell A4.
And enter a single dash line by pressing the repeating key which is the backslash and then
type a dash.
Your control panel should look like this.
Remember the repeating key is used to fill a cell with a character.
In this case we use the dash.
In release 3 the sheet is also indicated by a letter for the sheet, a colon and cell location.
Ignore the sheet letters for right now.
We will cover multiple spreadsheets in level 4.
Now press enter and watch the cell fill up with little dashes.
Let's fill cell A13 with dashes too.
And finally let's fill cell A16 with double dash lines.
The equal sign is the one we'll use here.
We'll copy all three of these lines all the way across to column G now.
Let's begin the double dash line in cell A16 because that is where the cell pointer is
sitting.
Press slash to display the main menu and press C to select copy.
Now 123 is prompting us for the range to copy from.
In this case we only want to copy the cell that we are currently on so press enter.
123 needs us to specify the copy to range now.
We want to copy the double dash line all the way to column G so let's move the cell pointer
to column B with the right arrow key and press the period to anchor it.
Now the cell pointer is anchored so we can expand it to highlight the rest of the cells
in this row that need a double dash line.
Press the right arrow key until the cell pointer is expanded to include cell G16.
Now press enter to finish the copy command.
Can you copy the other two lines on your own?
We'll perform the steps slowly so you can follow along.
Make sure you get caught up before we proceed.
If you are caught up now we will go on.
Move the cell pointer to cell B3 and look at the word above it.
See how the days of the week are too far to the left?
Let's write a line the days of the week, the word name and the words weekly totals all
at once.
Start by moving the cell pointer to A1.
Press the home key.
That will move the cell pointer there in one step.
Now press slash to display the main menu and R to display the range menu.
We'll use the labels option from this menu so press L to select it.
123 is prompting us to choose one of the three types of label alignment.
Press R to select write alignment.
Now 123 is prompting us to enter a range of labels to write align.
Press the right arrow key until column G is highlighted and then press the down arrow
key one time.
All the labels we need to change are highlighted so let's press enter to finish.
See all the labels we highlighted have now moved over to the right edge of their cells.
This should make our spreadsheet look better.
While the cell pointer is here let's enter the name of the person this expense report
is for.
Move the cell pointer to B1 and enter John Smith.
There does your spreadsheet look like this one?
If it doesn't take a minute to get caught up now.
Next we will write the formulas for this spreadsheet.
Start by moving the cell pointer to B9.
This is where we will total the meals for Monday.
Why don't we use the sum function to write this formula?
Type the at sign, the word sum, and a left parenthesis.
Now you can point to cell B5 with the up arrow key.
Once you have done that type a period to anchor the cell pointer.
Point to cell B7 with the down arrow key.
Type a right parenthesis to finish the formula and press enter.
See the formula is shown in the control panel and the correct answer which is zero right
now is shown in the spreadsheet.
Well Pam what do you think we need to do now?
I think we should copy the formula across the column G professor.
We could do that Pam but I am going to show you how to copy all the formulas at one time.
The next step will be to write the formula that subtracts beginning mileage from ending
mileage.
So move the cell pointer to cell B14 and begin the formula with a plus sign.
Now point to cell B12 with the up arrow key.
Type a minus sign.
Point to cell B11 and press enter to finish the formula.
Your formula should look like this one does now.
It reads plus B12 minus B11.
If it does not look the same take a minute and repeat the steps I just described.
If you remember how to use the edit key F2 you can correct the formula that way.
The next step is to write the formula for mileage expense.
This formula will be different than the formulas you have written in the past.
I am going to introduce you to absolute cell addressing.
From previous experience you know that a formula can be copied into one or more cells.
As an example the formula in this cell subtracts the number that is three cells above it from
the number that is two cells above it.
If you copy the formula across to calculate total miles from the other four days of the
week the formulas perform the same task as the original formula using the cells that
are three above and two above.
This is called relative cell addressing.
Our formula for mileage expense however is going to always refer to a rate that we will
type in one cell.
When we copy the formula to other cells we must make sure that the part of the formula
that refers to the rate cell does not change.
The first thing we will do is move the cell pointer up to cell H1.
Now let's type the word mileage.
Move down one cell, type the word rate, move down one cell and type decimal 21.
This is the rate per mile that we will use to calculate mileage expense.
With that done we need to move back to cell B15 and write the formula.
We will use the pointing method to write this formula and introduce a new function key.
The key is the absolute key F4.
To calculate mileage expense we need to multiply total miles times the mileage rate in cell
H3.
Type a plus to start the formula.
Move the cell pointer up one cell to the total miles figure.
Type a multiplication sign which is an asterisk and move the cell pointer to cell H3.
Your formula should read the same as ours does now.
It reads plus B14 times H3.
The cell pointer should be at cell H3.
Next we need to tell 123 to permanently lock onto this cell.
Press the function key F4 and look at the formula in the control panel.
See the two dollar signs, one in front of the H and the other in front of the 3?
They indicate that the cell address is now absolute.
Press enter to finish writing the formula.
The cursor is now back at B15.
If you're using release 3 you have three dollar signs.
The additional sign is in front of the sheet letter A.
The formula reads the same as usual except for the dollar signs.
We'll come back to this in a minute but first let's write the formula for total expenses.
We need to include three totals in this formula, lodging, total meals and mileage expense.
Move the cell pointer down to cell B17.
Press plus, move the cell pointer up to B3.
The lodging expenses, press plus, move the cell pointer up to B9, total meals.
Press plus, move the cell pointer up to B15 and press enter.
Check to see that your formula is the same.
If it isn't repeat the steps we just did before proceeding.
Now we have four formulas that need to be copied into the cells for the rest of the
week.
Do you think we can copy all of them at once?
Yes we can and it only involves one more step than the copy command you have been using.
To begin move the cell pointer up to the first formula in cell B9 which is meals.
Press slash to display the main menu and press C to select copy.
This is where we perform the extra step.
One two three is prompting us for the range to copy from.
If we were only copying one formula from one cell we would press enter.
But we want to copy all four formulas in this column so press the down arrow key until you
have highlighted all the cells down to total expenses in cell B17.
Don't be worried about these lines, they won't be bothered by the copy command.
Now press enter.
One two three is prompting us for the range to copy to now so move the cell pointer one
cell to the right.
Make sure it is in cell C9.
Anchor the cell pointer with the period and press the right arrow key until cell C9, D9,
E9 and F9 are all highlighted.
Press enter to finish the copy command.
There your screen should look like this.
If it doesn't go back and repeat the copy procedure again.
Remember the absolute cell address formula we used to calculate mileage expense?
Let's take a look at the way it looks.
Move the cell pointer down to cell B15 and read the formula in the control panel.
Now move the cell pointer into cell C15.
See the second half of this formula still refers to cell H3.
Take a minute to view the formulas in D15 through F15.
Did you notice that they also refer to cell D15?
Remember to use absolute cell addressing whenever you want to lock a formula onto a specific
cell.
The next thing we need to do is to write a formula that will total all five days lodging
expenses here in cell G3.
Again by moving the cell pointer to G3.
Let's use the sum function again.
Type the at sign followed by the word sum and left parentheses.
Now move the cursor pointer to cell B3.
Press the period to anchor the cell pointer and move the cell pointer back to cell F3.
Type a right parentheses to close the formula and press enter to finish.
Your formula should look the same as this one.
It reads at sum B3 through F3.
Take a moment to catch up now if you need to.
Now we are going to copy the formula into the meals row, total miles row, mileage expense
row and total expenses row.
We won't need to total the beginning and ending mileage rows so we will perform the copy command
in two steps.
Make sure the current cell is G3.
Press slash to display the main menu and C to select copy.
The range to copy from is G3 so press enter.
The range to copy to is G5 through G9.
Move the cursor to G5, anchor it, move it down to G9 and press enter to finish the copy
command.
Okay let's press the home key to see what we have.
Did you notice that we have an extra zero on cell G8 and there should be a line across
row 8.
Let's go through the steps to change this.
Move the cursor to cell A8, press backslash and a dash and press enter.
Press slash to display main menu.
C to select copy and enter because this is our copy from range.
Press the right arrow once, anchor the cursor with a period and move the highlight to cell
G8.
Press enter to finish the copy command.
See how the zero in cell G8 disappeared when we copied over it.
Now we are going to copy the formula in cell G3 to cells G14, G15, G16 and G17.
Make sure the current cell is G3.
Press slash to display the main menu and C to select copy.
The range to copy from is G3 so press enter.
The range to copy to is G14 through G17.
Move the cursor to G14, anchor it, move it down to G17 and press enter to finish the
copy command.
We will need to replace the zero in cell G16 with a double dashed line so move the cell
pointer to G16.
Type a backslash, an equal sign and press enter.
There we have our double dashed line again.
We have completed the working part of the expense report so let's take a look at the
numbers that need to be filled in.
Why don't you pause the tape now so you can fill in the expense report.
Remember the only numbers that we need to fill in are lodging, breakfast, lunch, dinner,
beginning mileage and ending mileage.
The totals will automatically be calculated because we've already written the formulas.
The next thing we will learn is how to format or change the appearance of numbers in cells
to look like dollars and cents.
Position the cell pointer on cell B3.
This is the upper left corner of the range of cells we want to appear as dollars and
cents.
All of the numbers in the expense report except beginning and ending mileage need to be formatted.
Press slash to display the main menu.
Press R to select range and F to select format.
Notice that 123 gives you several different formatting styles that you can use to change
the appearance of the spreadsheet.
Release 3 gives you even more formatting options in the other option.
The normal format is general.
We want to use currency so press C to select it.
123 is prompting us for the number of decimal places to display.
Notice that you can display from 0 to 15 decimal places and 2 is the normal setting.
We want to display 2 decimal places so simply press enter.
Now 123 is prompting us for the range to format.
Move all the lodging numbers over to column G and then highlight down to the total meals
row, row 9.
The range to format should read B3 through G9.
Press enter to finish the format command.
That was pretty easy wasn't it?
Let's format mileage expense and total expense as currency now.
Move the cell pointer to cell B15 and press slash to display the main menu.
Press R to select range, F to select format, and C to select currency.
Press enter to accept 2 decimal places and highlight the range B15 through G17.
Press enter to finish formatting the range.
Now that we have completed the expense report we will save it.
Press slash to display the main menu and press F to select file and S to select save.
123 is prompting us for the name of the file.
Let's call this expense report Smith 1 and press enter.
One of the things you can do with 123 is to combine the contents of different files and
either consolidate information or add numbers together.
Suppose you wanted to keep each week's expense report for John Smith and then at the end
of each month total all his expenses.
We are going to modify the current expense report so we can have two weeks of expenses
and a master expense report to combine our weekly totals in.
I don't want you to have to do a lot of typing so let's start by changing a few of the numbers
in the current expense report and pretending it is the report for week number 2.
Start by erasing the lodging expense for Wednesday.
Move the cell pointer to cell D3.
Press slash to display the main menu, R to select range and E to select erase.
When 123 prompts you for the range to erase press enter.
Do the same thing to the dinner expense for Friday in cell F7.
Remember we are only using these numbers for illustration.
Normally the mileage figures would all change and entries would have to be made for each
day's lodging and meals.
When you combine spreadsheets you can combine the whole spreadsheet or you can combine what
is called a named range.
We are going to use named ranges so we will have to take a minute and name two ranges
of cells that will be combined from Smith 1 and Smith 2.
Since we are here let's start by naming the ranges on this spreadsheet and then we'll
save it and call it Smith 2.
The first range of cells we want to combine includes all the expenses we entered for lodging
and meals and the second range is the total miles row.
Move the cell pointer to cell B3 first.
Now press slash to display the main menu.
Press R to select range, N to select name and C to select create.
123 is prompting us for the name of the range.
You can type a name up to 14 characters long.
Let's call this range expenses.
Type the word expenses and press enter.
123 is prompting us to highlight the range so move the cell pointer to cell F7 and press
enter.
To name the second range move the cell pointer to cell B14.
Press slash to display the main menu, press R to select range, N to select name and C
to select create.
123 is prompting us for the name of the range.
Let's call this range miles.
Type the word miles and press enter.
123 is prompting us to highlight the range so move the cell pointer to cell F14 and press
enter.
Now that we have named both ranges we will save this expense report so that we can have
two weeks expenses to combine into the master file.
Press slash to display the main menu.
Press F to select file and S to select save.
123 is prompting us for the name of the file.
Let's call this expense report Smith 2.
Type the file name now and press enter.
We need to create the same range names for the same groups of cells in Smith 1 so let's
retrieve it now.
Do you remember how to retrieve a file in Lotus 123 Pam?
I push slash to display the main menu, select file, then retrieve and then type Smith 1
and finish with enter.
That's exactly right Pam.
Now we can name the same ranges of cells in this spreadsheet that we named in Smith 2.
In fact we will use the same names because that will help us remember them.
Start by moving the cell pointer to cell B3.
Next press slash to display the main menu.
Press R to select range, N to select name and C to select create.
123 is prompting us for the name of the range.
Type the word expenses and press enter.
123 is prompting us to highlight the range so move the cell pointer to cell F7 and press
enter.
To name the second range move the cell pointer to cell B14.
Press slash to display the main menu, press R to select range, N to select name and C
to select create.
Let's call this range miles.
Type the word miles and press enter.
123 is prompting us to highlight the range so move the cell pointer to cell F14 and press
enter.
Now we will save this expense report again.
Press slash to display the main menu.
Press F to select file and S to select save.
123 is prompting us for the name of the file.
Smith 1 is the file name and is displayed here in the control panel.
All we need to do is save it again so just press enter.
Lotus now gives us a choice of backing up the file if we have already saved it once.
Choose B to make a backup of the old file and save the new file or R to erase the old
file and replace it with a changed version of our spreadsheet.
The backup option is valuable because you will have another version of your work in
case you make a mistake on your latest version.
Press R to confirm and the file is saved.
Now we need to combine Smith 1 and Smith 2.
Before we can do that we need to create a spreadsheet that doesn't have any expense
figures in it.
We will then bring the expense figures into the blank spreadsheet with the file combine
command.
I know an easy way to create this new spreadsheet.
We will erase the exact same ranges of cells that we used for the two named ranges.
Then we will save the spreadsheet again and give it another name.
That will give us three expense reports.
One for Smith 1, one for Smith 2, and one we will call totals.
We will erase the first range using the range name we already created.
Start by pressing slash to display the main menu.
Press R to select range and E to select erase.
One two three wants to know the range to erase.
You could type the word expenses and one two three would erase the range called expenses.
But let's see what to do if we can't remember the name of the range.
Press the function key F3 and the list of range names will appear right here in the
control panel.
You can use the arrow keys to highlight range names in the control panel the same as with
any one two three menu.
For now since expenses is highlighted press enter.
There we erase the exact range of cells we wanted.
Erase the range name miles the same way.
Press slash to display the main menu.
Press R to select range and E to select erase.
One two three wants to know the range to erase.
Press the function key F3 to display the list of range names.
Highlight miles and press enter.
We still want to erase the mileage figures because they won't have any meaning as we
accumulate the weekly expenses.
So move the cell pointer to cell B11 and press slash to display the main menu.
Press R to select range and E to select erase.
Move the cell pointer to cell F12 and press enter to finish erasing the range.
There we have created the blank areas we will use for combining the two expense reports.
Now we need to save this spreadsheet under the name totals.
Press slash to display the main menu.
Press F to select file and S to select save.
One two three is prompting us for the name of the file.
Smith 1 is the displayed file name.
Begin typing totals and notice that Smith 1 disappears as soon as you type the first
letter of totals.
After you type the file name press enter to finish the save command.
The spreadsheet on the screen is totals.
Smith 1 is still intact on the disk.
Our next step is to combine the range called expenses from Smith 1 and Smith 2 into the
blank area of the current expense report.
The cell pointer must be positioned at the upper left corner of the area to be filled
by the file combine command so move the cell pointer to cell B3.
Press slash to display the main menu.
Press F to select file and C to select combine.
One two three wants to know if we want to copy, add or subtract the incoming cells.
We will replace the current cell contents with the incoming cell contents.
Add will add the incoming cell contents to any existing numbers in same cells of the
current spreadsheet and subtract will subtract the incoming cell contents from the same cells
of the current spreadsheet.
We're going to combine Smith 1 first.
We must use add when we do this type of combine because we need to bring the numbers from
Smith 1 and Smith 2.
If we were to use copy for the miles range, the formulas would be combined and the spreadsheet
would display zeros in the miles row.
Press A to select add.
One two three is prompting us to select an entire file or a named range to combine.
We will use the named range so press N to select it.
Now one two three is prompting us for the range name to combine.
The first range we want to combine is expenses.
So type expenses and press enter.
The names key F3 does not work with this command.
Now we need to specify the file name to combine.
It is Smith 1 so type Smith 1 and press enter.
That was fast wasn't it?
Not only did the numbers from Smith 1 come in but the formulas in the current spreadsheet
calculated the totals for us.
We need to repeat the procedures so we can combine the miles range also.
Move the cell pointer to cell B14.
Press slash to display the main menu.
Press F to select file and C to select combine.
Press A to select add.
One two three is prompting us to select an entire file or a named range to combine.
We will use the named range so press N to select it.
Now one two three is prompting us for the range name to combine.
The second range we want to combine is miles so type miles and press enter.
Now one two three wants to know the name of the file we want to combine so type Smith
1 and press enter.
Now that we have completed combining Smith 1 we can combine the same information from
Smith 2.
Make sure the cell pointer is in cell B3 before you begin.
Press slash to display the main menu.
Press F to select file and C to select combine.
One two three wants to know if we want to copy, add or subtract the incoming cells.
Press A to select add.
One two three is prompting us to select an entire file or a named range to combine.
We will use the named range so press N to select it.
Now one two three is prompting us for the range name to combine.
Remember it was expenses so type expenses and press enter.
Now we need to specify the file name to combine.
It is Smith 2 so type Smith 2 and press enter.
The miles range from Smith 2 still has to be combined.
Move the cell pointer to cell B14 before you begin.
Press slash to display the main menu.
Press F to select file and C to select combine.
One two three wants to know if we want to copy, add or subtract the incoming cells.
Press A to select add.
One two three is prompting us to select an entire file or a named range to combine.
We will use the named range so press N to select it.
Now one two three is prompting us for the range name to combine.
Type miles and press enter.
Now we need to specify the file name to combine.
It is Smith 2 so type Smith 2 and press enter.
There both expense reports have been combined into an accumulated two week expense report.
Of course this expense report format probably isn't suited to your needs.
It was designed only as a learning tool.
Professor, why are all these stars in the total column?
Whenever a number is wider than a cell Pam, one two three fills the cell with asterisk
or stars as most people call them.
All we have to do to fix this is make column G wide enough to display the numbers.
Move the cell pointer to column G and press slash to bring up the menu.
W to select worksheet.
C to select column and S to select set width.
Now press the right arrow key until the numbers reappear and then press enter.
That's all there is to it.
Before we go on with the rest of the lesson, we should save our totals spreadsheet.
Press slash to display the main menu.
Then press F to select file and S to select save.
One two three is suggesting the file name totals now.
So all we have to do is press enter and one two three wants to know if we want to cancel
or replace.
Press R to replace.
Now we can continue with the rest of this lesson.
I think we have spent enough time with the expense report, so let's erase the worksheet.
Begin by pressing slash to display the main menu.
Press W to select worksheet and E to select erase.
We need to confirm by pressing Y to select yes.
Remember you already saved the spreadsheet that was on the screen so nothing was lost
or damaged by erasing the worksheet.
I like to think of it as cleaning up my desktop.
Lotus 123 can also be used to manage information such as phone lists, inventories, and employee
payroll rosters.
The information can be sorted to appear in the order you want.
Pieces or groups of information that meet certain conditions can be found or extracted
and reports can be printed that only include the information you want.
Organized groups of information are called databases.
The address list on the screen right now is a database.
The first row of a 123 database consists of field names.
A field name is a word that describes the contents of all the entries in the columns
below it.
This database has a field name for last name, first name, address, city, state, and zip
code.
Each row on the database that contains actual information is called a record.
All the rows together are called a database or a file.
The record rows must begin one row beneath the field names.
We are going to create the database that was on the screen, but first we need to make column
C and D a little wider so the address will fit.
Move the cell pointer to column C. Press slash to display the main menu, W to select worksheet,
C to select column width.
Press C for column range and S to select width.
123 is prompting you for which columns, so press the right arrow to column D and press
enter.
At the columns width prompt, type 12 and press enter.
There is one thing you need to know before you begin.
The addresses all begin with a number.
If you type a number followed by a word, 123 will beep at you.
Display the word edit in the mode indicator and display the address in the control panel
with the cursor blinking between the number and the text.
123 is not able to interpret mixed numbers and letters if the entry begins with a number
unless you use a label alignment character as the first character of the entry.
Let's type the address again using the left label alignment character which is an apostrophe
to tell 123 that we are typing a label.
There, now 123 will accept the entry.
Make sure you type the apostrophe for every address.
You also need to type the entries with the capital letters the same way.
When you search for information in a 123 database, it is very particular about capital letters
and lower case letters.
Do this again by using the same key.
Now move the cell pointer to cell A1.
You should be able to enter the information in this phone list on your own.
I am going to display the list on the screen so you can pause the recorder long enough
to type the list.
Notice how the address list is displayed with the records in random order.
If we want to arrange the records in order by city and also alphabetize the people's
last names, we will use the data option on the main menu and sort the records in the
address list.
Press slash to display the main menu, D to select data, and S to select sort.
Now 123 needs at least two pieces of information from us.
The first piece of information we must apply is the data range.
The data range consists of all the records excluding the field names in the row at the
top of the database.
Let's set the data range now.
Press D to select data range.
123 is prompting us to highlight the data range now.
So move the cell pointer to cell A2 and press a period to anchor it.
Set the range down to cell F10 and press enter.
Did you notice that 123 returns to the data menu?
We perform several tasks with this menu, so 123 always returns to this menu when you finish
one of the related tasks.
The primary key is used to sort a database in one level.
As an example, if we only wanted to sort the address list by city, we would only need to
specify the primary key.
We want to alphabetize the list by last name within city, so we will specify the primary
key of city and a secondary key of last name.
Press P to select primary key.
123 displays the address of the current cell in the control panel after the message primary
sort key.
Move the cell pointer to the word city at cell D1 and press enter.
Now 123 wants to know if we want to sort in ascending order or descending order.
Ascending order is from lowest to highest or A to Z for letters and 0 to 9 for numbers.
Descending order is from highest to lowest or from Z to A for letters and 9 to 0 for
numbers.
Notice that D is the suggested choice, a descending sort.
We want an ascending sort, so type A and press enter.
The sort menu is displayed again and we want to specify a secondary sort, so press S to
select secondary sort.
123 displays the address of the current cell in the control with the prompt message secondary
sort key.
L name is the first field.
So move the cell pointer to cell A1 and press enter.
We want to sort in ascending order again, so type an A and press enter.
Users of Release 3 have another added bonus here.
See the extra key?
In addition to primary and secondary sorts, you can have another 253 sorts.
That's certainly more than we need right now.
The last step is to tell 123 to perform the sort.
You probably guessed it already.
Press G to select go and sort the database.
If you are looking over here at the last name field and wondering why the first record starts
with a W, remember that we sorted by city first.
Look over here in the city field.
They are all alphabetized.
Now, if you will look at the last names for Dallas, Reno, and Seattle, you will see that
the last names are alphabetized within each city.
That is all there is to sorting a database.
There are two things you should remember.
Once you specify a data range and save the database, 123 remembers, so you don't have
to reset the data range every time you want to sort.
The other thing you need to remember is that if you add records, you will need to expand
the data range to include the new records before performing a sort, or 123 will ignore
them.
When you want to search a database and find one record such as the record for Mary Jones
or find all records that have an equal value in one field, such as all people who live
in San Diego, you must set up an input range which includes all of the database including
the field names and a criterion range before you can search through the database.
In Lotus 123, the word used for all search operations is query and is found on the data
menu.
Before we use the query menu, we are going to create the criterion range headings.
The criterion range consists of a row of field names identical to the field names at the
top of the database and one or more blank rows underneath the criterion range field
names.
The criterion range can be placed anywhere on the spreadsheet where there is room.
We will place it underneath our database.
Why don't we copy the field names that we already have?
It will save some typing and we aren't as likely to make mistakes.
Move the cell pointer to cell A1.
Press slash to display the main menu and C to select copy.
The range to copy from is the whole row of field names, so press the right arrow key
to highlight the cell F1 and press enter.
123 is prompting us for a range to copy to.
Let's copy the field names in row 12.
Move the cell pointer to cell A12 and press enter.
There, we now have a duplicate of the field names that we will use for the criterion range.
That is the only preliminary step necessary before going to the query menu to set up the
input and criterion ranges.
We will use the following procedure to set up the input range.
Press slash to display the main menu.
D to select data, Q to select query, and I to select input.
123 is asking for the input range.
It is very important to remember the difference between the data range you use to sort the
database and the input range.
The database range did not include field names.
The input range that we are going to specify now does include field names.
If the cell pointer is not in cell A1, press home to move it there and anchor it with the
period.
Now highlight the whole database.
The input range should read A1 through F10.
Press enter and we are returned to the query menu.
Next, we will set the criterion range.
Press C to select criterion.
Again, we will need to specify the range.
Our criterion range consists of the row of field names at row 12 and the blank row underneath
them at row 13.
So move the cell pointer to A12.
Anchor it with the period and expand the cell pointer to F13.
Press enter to finish this step.
The blank row is where we type the information we would like 123 to find for us.
The query menu is still displayed, but we have to enter our search criterion before
we can find anything.
So let's press Q to select quit and return to the ready mode.
We are going to search for all the people in the database with the last name of Jones.
So move the cell pointer to cell A13, which is the first blank cell under last name in
the criterion range and type Jones.
Make sure you type it exactly the way you expect it to be in the database and press
enter.
Now that we have something to search for, let's query our database.
X slash to display the main menu, D to select data, Q to select query, and F to select find.
You see the record containing the last name Jones is highlighted.
Try pressing the up arrow or down arrow keys now.
123 beeps at you and the highlight does not move, does it?
There is only one record in the database with the last name Jones.
If there were more, the highlight would jump to the next record with the last name Jones.
Now we must press escape to get back to the query menu and Q to select quit and return
to the ready mode.
It is not necessary to go through the data query menu procedure to perform subsequent
searches.
It would be very inconvenient if you wanted to do several searches in a row.
The function key F7 can be used to repeat the last query command.
Before we use F7, let's change our search criterion.
Instead of searching for last name, why don't we search for a city?
Move the cursor to cell A13 and press slash, R for range, E for erase, and enter to finish.
This will get rid of our last search criterion.
Now move the cell pointer to cell D13 and enter the word Seattle.
When you are ready, press F7.
There the first record where the city is Seattle is highlighted.
Press the down arrow key now and the other record containing the city Seattle is highlighted.
Press the down arrow key again.
That's right.
1, 2, 3 beeps at you indicating that there are no more records in that direction that
satisfy the query condition.
Press the up arrow key twice and you will see that there are no more records that satisfy
the query condition either.
When you are done looking at records, press escape to return to the ready mode.
You can also perform multiple criterion searches.
If you type a last name and a first name in the criterion range, 1, 2, 3 would find only
those records where both conditions were satisfied.
If you type a last name in two rows, 1, 2, 3 would find all records where either last
name was found.
You would have to expand the criterion range by one row to perform a search where either
name would be found.
Now suppose you wanted to extract certain records from the database so you could create
a report or perform some other task with them.
This picture shows an output range with both records where the city is equal to Seattle.
The input and criterion ranges remain the same and an output range is required.
The output range is similar to the criterion range except that it includes only one row.
Let's create the output range and extract all the records where the city is equal to
Seattle.
First we will copy the row of field names from the criterion range down to row 15.
Move the cell pointer to cell A12.
Press slash to display the main menu and C to select copy.
Select all the field names and press enter to select the range to copy from.
Move the cell pointer to A15 and press enter to finish the copy command.
All we have to do now is tell 1, 2, 3 where the output range is and perform the extraction.
Press slash to display the main menu, D to select data, Q to select query, and O to select
output.
1, 2, 3 prompts for the output range.
Move the cell pointer to A15 if it is not already there.
Anchor it with the period and highlight all the field names on that row.
Press enter to finish specifying the output range.
With the query menu still displayed, all we need to do is press E to select extract and
watch the results.
Both records where the city is Seattle are displayed under the output range.
This will really help me with my phone list, Professor.
I think I could use the database every day.
Well Pam, we only scratched the surface of the database's capabilities, but even with
these basic tools, you were right about the convenience of an electronic phone list.
Before we proceed, press Q and go back to the ready mode.
The 1, 2, 3 database commands can be used to create some very sophisticated reports.
The basics that you have used today need to be practiced because as you can see from the
exercise we just did, each step must be performed before you can get results.
Now I want to talk to you about printing a report using the information we just extracted
from the database.
Any range of cells can be printed in 1, 2, 3.
I chose the block of information to print so that I can teach you some advanced printing
commands, not because it has to be done while using the database.
Remember a Lotus 1, 2, 3 database is nothing more than a spreadsheet that is arranged according
to a specific layout, which provides the ability to sort, find, and extract records.
Suppose we wanted to print this group of cells.
We already know that all we have to do is choose the print command, highlight the range
we want printed, and select go from the print menu.
If we want to have a header with the current date and a page number like this one, we can
use the print options header and command to set it up.
Let's do that now.
Let's move the output range up so you can see it better.
Move the cell pointer to cell A15 so we can easily specify the print range when we are
ready.
Press slash to display the main menu, P to select print, and P to select printer.
The print menu is displayed.
We need to specify a print range and set up our header before we print the report.
Start by specifying the print range.
Press R to select range.
1, 2, 3 prompts us for the range to print.
Notice that the cell pointer is not anchored, so press the period to anchor it and highlight
from A15 to F17.
Finish with the enter key.
The next step will be to set up a header.
Press O to select options and H to select header.
1, 2, 3 is requesting a header.
A header can only be one line long and it is divided into a left section, center section,
and a right section.
When you type the header, separate each section with the pipe symbol.
It is this split vertical line on the same key with the backslash.
Let's type a header similar to the one that was just on the screen.
Type the word date followed by a colon and a space.
Now type an at sign.
This tells 1, 2, 3 to insert the date that is set in the computer whenever it prints
this header.
Next, separate the date from the report title by typing a pipe.
Then type the title of the report, Seattle Members.
Then type another pipe to separate the title from the page number.
Type the word page, a space, and a pound sign.
The date will print against the left margin.
The report title will print in the center of the page and the page number will print
against the right margin.
Press enter to return to the print options menu and escape to return to the print menu.
Go ahead and print the report if you wish.
There is one more print option that is very useful.
It is called borders.
In addition to a header and a footer, you can specify one or more rows or columns that
will print on every page of the report.
Suppose that you wanted to print the same report we just printed, but it was several
pages long.
The header we created will print at the top of every page, but the field names will only
print at the top of page 1.
By changing the print range to not include the field names and setting the field name
row as a border, you can cause it to print on every page of the report.
Here is how to do it.
The print menu should still be on the screen.
Let's change the print range first, so press R to select range.
One two three shows the last print range we specified.
Press escape and the print range becomes unanchored and the cell pointer goes back to cell A15.
Move the cell pointer to A16.
Anchor it by pressing the period and expand the print range to cell F17 and press enter.
The next step will be to set up the border row.
Press O to select options from the print menu and B to select borders from the options menu.
Now one two three is prompting us to specify columns of rows for a border.
Release three gives us an added border option, frame.
You can also print the frame or the column letters and row numbers on your spreadsheet.
Press R to select rows.
You can have one or several rows in a border if you wish.
If the cell pointer is not on row 15 move it there and press enter.
Now that the options menu has reappeared press Q to select quit and return to the print menu.
If you print now the field names will appear on the report the same as they did before.
If you want to see how borders print you will have to create a report that will be longer
than one page.
For now let's move on to our next subject, but first press Q to return to the ready mode.
You can save the database if you like.
It might be nice to refer back to it later.
Our final lesson today will teach you to create a graph that you can view on the screen.
If you don't have a color or a graphics monitor you won't be able to see the results of this
lesson.
Graphs are used to present all sorts of business results, facts and trends.
There are several types of graphs that can be created with one two three.
The first type is a bar graph.
The second type is a pie chart.
The third is an XY graph.
The fourth is a stacked bar graph.
The fifth is a line graph.
One of the strengths of release three is the added graphics capabilities.
There are a number of new graph types you can create as well as a number of added graphing
options.
We will cover graphics in more detail in another lesson.
Today we are going to create a simple bar graph and a simple pie chart using the same
information.
Before we begin let's clear the screen and enter some names and numbers so we can create
our graphs.
Press slash to display the main menu, W to select worksheet, E to select erase and Y
to confirm.
Let's enter the data shown in this simple example.
At A1 enter Tom.
At B1 enter Mary.
At C1 enter Joe.
At C2 enter 289.
At B2 enter 652.
And at A2 enter 345.
Now we have the raw materials necessary for a graph.
We will create a bar graph first.
Lotus one two three will require us to specify the type of graph we want to view.
Then we need to specify the X range.
The X range includes the names of the data items we want plotted.
In this case the X range will be the names in row one.
Then we need to specify the A range which is the first data range, the numbers in the
second row.
Shall we create our graph now?
Press slash to display the main menu and G to select graph.
Notice that we can plot up to six different data sets of data in a graph.
The X range is always used for labels.
Start by pressing T to select type and B to select bar.
The graph menu returns so let's press X so we can specify the X range.
One two three is prompting us for the X range so move the cell pointer to A1.
Press the period to anchor it, highlight all three names and press enter.
Now the menu is displayed so press A to specify the first data range A.
If the cell pointer is not on cell A2 move it there, anchor it, highlight the rest of
the numbers in the row and press enter.
Everybody look at the screen and press V so we can view our masterpiece.
This is some graph isn't it?
These bars in the middle represent the numbers from the spreadsheet.
The names of the data ranges appear under each bar and this is the Y range.
It is generated automatically starting at zero and going to the next higher number above
the highest number in the data range.
You can add legends, titles and grids to a graph to make it more effective.
Each graph that you create can be named and kept for a future use.
You must save the spreadsheet after you name graphs.
You can also save individual graphs as picture files.
Picture files can be printed from the Lotus print graph program.
Press the space bar to return to the graph menu when you are done.
The next step is very simple.
We will change to a pie chart and view it.
Press T to select type and P to select pie.
The graph menu returns so press V to view the pie chart.
Notice that there are no numbers on this chart.
A pie chart shows what percent of the whole each data element is.
Press the space bar to return to the graph menu and Q to return to the ready mode.
This just gives you an idea of the time savers you can create with just a little practice.
Well professor, I've really learned a lot today.
You've made it easy.
Thank you.
Thank you for being such a good student Pam.
On the screen now are some other tapes available in my series.
Thank you very much.