NOS : Certificate in Computer Applications
| Home | Table of Contents |

Up: Online Course Material

16.1 INTRODUCTION

You know that we can enter numbers, label or formula in a cell. Very often we have to copy this number, label or formula in to other parts of the worksheet. At times you may feel that the content of a cell should be shifted to another cell in the worksheet. For performing these types of jobs we use two very important commands in 1-2-3. They are i) COPY and ii) MOVE. In this lesson we will discuss these two commands in detail.

16.2 OBJECTIVES

At the end of this lesson, you should be able to:

• edit, copy and move the data in the worksheet
• understand the utility of relative addressing
• know the difference between relative and absolute addressing
• have an idea about mixed addressing
• save the worksheet and Quit 1-2-3.

This command is used to copy labels, numbers and formulas from one part of the worksheet to another. The use of this command can save a lot of effort and time whenever a similar set of labels, values or formulas is required at two or more places in a worksheet. Fig. 16.1 depicts the menus available under this command.

When the /C command is selected, 1-2-3 issues the prompt "Enter range to copy From". When you type in the range of cells to copy from and press the ENTER key, you will find another prompt which reads "Enter range to copy To". You are expected to enter the desired range and complete the command by pressing the ENTER key.

We will give some examples below to illustrate the use of the Copy command. For these examples, consider a worksheet, which resembles the following:

Let us copy from range be A1..A1 and the copy to range D1..D1. The resulting worksheet will look like:

Suppose you want to copy the contents of cells A1, A2 and A3 to D1..D3. Hence you have to specify the "From" and "To" range as A1..A3 and D1..D3 respectively. Consequently the spreadsheet will look like:

3. Copy From a Range to a Larger Range

In the previous example the number of cells in the copy from and copy to was equal. Now let us look in to an example where the number of cells of 'copy to' is more than the number of cells in 'copy from'. If the "From" range is A1..A1 and the "To" range is D1..E1 the resulting worksheet will be as depicted below:

Fig. 16.5

In this case the "From" range is repeated within the "To" range as many times as needed to completely fill the "To" range.

If the "From" range is A1..B1 and the "To" range is D1..D1, then the cell E1 will be overwritten. The worksheet will look like:

This examples also illustrates the fact that when the "To" range indicated is smaller than the "From" range, then 1-2-3 will overwrite enough cells to the right of (and below) the indicated "To" range to accommodate the entire From range.

If the "From" and "To" ranges overlap, then 1-2-3 considers only that potion of the "From" range that does not overlap. This is illustrated below in Fig 16.7 The "From" and "To" ranges specified in our continuing example are A1..B3 and B1..C3 respectively.

Copying formulas is an essential part of the use of 1-2-3. To copy formulas containing cell references correctly, one must have knowledge of the cell addressing techniques 1-2-3- uses. By default, cell references are copied relative to the formula cell. Now in the following section, we will explain how this is done and how to change relative addressing to absolute or mixed addressing schemes.

Lotus maintains the address of a particular cell and compares it to the origin of the worksheet. For example, in relation to a formula in cell E10, cell G4 is two columns to the right and six rows above cell E10. To make this clear, look at Fig.ure 16.8. In cell B11 we have the formula +B7+B8+B9+B10. If we copy this formula to cell C11, Lotus is smart enough to recognize that in this cell we have to add +C7+C8+C9+C10. If we copy the same formula to cells D11 and E11, in cell D11 we will see +D7+D8+D9+D10 and in cell E11 we will see +E7+E8+E9+E10. This is called relative addressing, a powerful feature. It will make the task of copying a more efficient operation. Let us say, you have sales data related to 100 different businesses in the first 100 columns of a worksheet. To calculate the sum of each column, all you need to do is type a formula for one column and then copy the same formula to the other 99 columns.

(b) Absolute Addressing

There are many times when you must refer to an exact location or an exact value. Sometimes you may want to use some predefined numbers or ratios. In these cases you have to use absolute addressing.

To make the distinction clear, look at Fig. 16.9. Five divisions of XYZ Company has sold different amounts of a particular product. Your task is to calculate the percentage of total sales for each division. In cell C5, type formula +B5/B11. If you copy this formula to range C6..C9, you will get an error. The reason is that in every case the division unit must be divided by the total unit currently in cell B11. Relative addressing will not work here. You have to make cell B11 absolute, meaning always fixed.

To make a cell absolute, put a dollar sign ($) before the row number and another dollar sign before the column (for example, $A$1). You can either type ($) or use F4, the Abs function key. If you type $B$11 then cell B11 becomes absolute.

Column B in Fig. 16.9 shows the sales by 5 divisions of XYZ company. Sales/Total gives the share of sales for each division. If we type +B5/B11 and copy it to C6..C9 there will be errors. Hence, first we type (+B5/$B$11) in cell C5, then copied this formula to range C6..C9.

Top

IN-TEXT QUESTION 16.1

1. Define the following terms briefly:
2.  

(a) Relative Addressing

(b) Absolute Addressing

3. The current cell B6 contains the formula @SUM (B1+B2+B3+B4+$B5). When the contents of B6 are copied into D8..D8 reads:

(i) @SUM (D1+D2+D3+D4+$B5) True/False

(ii) @SUM (D3+D4+D5+D6+$D7) True/False

(iii) @SUM (D3+D4+D5+D6+$B5) True/False

(iv) @SUM (D3+D4+D5+D6+$B7) True/False

Top

There are cases when you want to have both relative and absolute addressing together. You can have the row relative and the column absolute. Otherwise you can keep the column relative and the row absolute. For example, $A8 means that the column remains the same but the row changes. On the other hand B$8 means that row is fixed but the column changes. Fig. 16.10 illustrates an example of mixed addressing. In the figure we show the discount prices under different discount rates for two products whose original prices were Rs.12 and Rs.30 In the table horizontally we give the discount rates such as 5%, 15%, 25%,…55%. We have to calculate the after-discount price at these discount rates.

In the above example the absolute row reference did not change while the relative column address got changed accordingly.

The MOVE command physically moves the contents of a cell or a range of cells to the specified range as shown in Fig. 16.11 and Fig. 16.12. The method of moving the contents of a range at the desired location is same as copying the contents of a cell or cell range. But the move command erases the contents of the specified range from its current position and moves it to the new position. Suppose you want to move the contents of the range A1..D9 to the range A4..D12. Fig. 16.11 shows the original worksheet and Fig. 16.12 shows after the range is moved. The steps are as follows:

1. Invoke the command menu by using /

2. Type M

3. The computer will prompt: Enter range to Move from

4. Type in the range which is to be moved (e.g., A1..D9)

5. Press ENTER Key

6. The computer will prompt: Enter range to move to. Specify the range address

where the data will be moved (e.g., A4..D12). Press ENTER key.

In Fig. 16.12 the range of cells A1..D9 is moved while other cells remained unchanged.

Top

IN-TEXT QUESTIONS 16.2

1. Is it possible to recover the data when we copy data on to a column already containing data?
2. What is move command?
3. What is the effect of move command, when we move a range on to a range containing data?

After having created a worksheet, you may want to save it on the hard disk or on the floppy disk for future use. Moreover, while you are working with a worksheet, it is good to periodically save the worksheet, so that in case of a power failure, the last saved version of the worksheet can be retrieved. To save the worksheet /File Save command is used. At this stage, 1-2-3 expects a file name under which the current worksheet could be saved. It displays the names of the existing worksheet files on the control panel. Since you are saving your file for the first time, you type in the file name and press ENTER. File name can contain a maximum of 8 characters consisting of alphabets, digits and underscores. No blank space is allowed in file names. The file thus saved is automatically gives extension .WK1 by version 2.0 of 1-2-3. If you want to save the worksheet on a disk other than the current disk, then type the file name preceded by drive name and press ENTER. In future, you want to save the current worksheet under the name of its previous version, you can choose the existing file name from the file names displayed on the control panel by using Right or Left Arrow Key and pressing ENTER. The 1-2-3 prompt at this stage is:

Cancel Replace

If you had accidentally chosen a wrong file name, you can cancel the command by selecting Cancel, else replace the named worksheet file by the current one by selecting Replace.

Having saved your file, you might want to quit 1-2-3 and either switch off your computer or work on another software. To quit from 1-2-3, invoke the command /Quit. Lotus 1-2-3 responds by giving the following options:

No Yes

with the highlighted bar at No, select option Yes to quit. Please bear in mind that if you try to quit 1-2-3 without saving your worksheet, 1-2-3 does not give any warning - the only safety is that it asks you to confirm the Quit command by selecting Yes option. The system now returns to the Lotus Access Menu from where select Exit to return to DOS.

Top

In this lesson, we have discussed the usefulness of two important and powerful commands in LOTUS 1-2-3 - copy and move. Utility of Relative, Absolute and Mixed addressing has also been explained. It is also stressed that worksheet file must be saved periodically to guard against power failure or any other mis-happening.

1. What is the difference between copy and move commands?
2. What do you mean by mixed addressing?

IN-TEXT QUESTIONS 16.1

1. (a)Relative Addressing: An addressing scheme where cell references are stored relative to the formula cell.

2. Absolute Addressing: An addressing scheme where cell references are absolute and do not depend on the position of the cell relative to the formula cell.

2. (i) False

(ii) False

(iii) False

(iv) True

IN-TEXT QUESTIONS 16.2

1. No.
2. The Move command enables a range of cells to be moved from one location to another in the worksheet. We delete information from one location and insert it to get it into another location.
3. When we move the contents of a column to another column the existing contents in the "To" cell(s) are destroyed. Move command is similar to copy command but the range from which information is moved is replaced by blanks.

Top