For best results: this site requires that cookies be enabled for proper operation - see Legal Page for more info

Select Any of These

Translate this page      using FreeTranslation.com

Lotus SmartSuite 1-2-3 tips

LAST UPDATED: Thursday, 08 November 2007 18:29:52 -0600

TOTAL THIS    USING TOTAL IN 1-2-3    RELOCATING THE LOTUS 1-2-3 WORK FILES

PASTING CHARTS INTO WORD PRO DOCUMENTS    NAVIGATING IN 1-2-3

LEAP YEARS IN 1-2-3    ENTERING DATA QUICKLY INTO SELECTED 1-2-3 CELLS

FAST FORMATS IN 1-2-3    AN INTERESTING 1-2-3 PROBLEM    CALCULATING WEEKDAYS IN 1-2-3

CALCULATING WORKDAYS IN 1-2-3    CHANGING THE TAB KEY ACTION IN 1-2-3

CROPPING THE 1-2-3 MAP    COLORING 1-2-3 TABS    MORE COLORING IN 1-2-3

ENTERING TIME IN A 1-2-3 WORKSHEET    ADDING SHEETS TO A 1-2-3 WORKBOOK

TRANSPOSING DATA IN 1-2-3    CHANGING 1-2-3 WORKSHEET FORMATS

WORKING WITH SPECIFIC NUMBERS IN 1-2-3    USING UNDO IN 1-2-3

OPENING THE 1-2-3 INSERT DIALOG BOX    SELECTING MULTIPLE RANGES IN 1-2-3

SORTING BLANK CELLS IN 1-2-3    SPRUCING UP 1-2-3 CHART COLORS

HIDING CELLS IN 1-2-3    CALCULATION ACCURACY IN 1-2-3

TOTAL THIS

"A friend claims you can get 1-2-3 to provide a total by simply typing in the word Total. I'm so sure this is impossible that I have a bet on it. Please tell my friend there is no way to get a total by simply typing in the word."

Sorry, you lose. Here's how to prove it to yourself. Say you make up a table with three names that shows the sales figures for the months of April, May, and June. Let's suppose you enter your numbers in cells B1..B3, C1..C3, and D1..D3. Go to cell A5 and type Total

When you press one of the arrow keys, 1-2-3 automatically places the Sum formula in cells B5, C5, and D5. You don't need to type the @sum formula; 1-2-3 takes care of the job for you.

USING TOTAL IN 1-2-3

Here's a 1-2-3 question:

"I have seen several tips talking about using the word Total in 1-2-3 to sum a row of numbers. This does not work for me. I am using 1-2-3 97. Do these tips refer to a newer version of 1-2-3?"

No, you can use the tip with 1-2-3 97. It sounds like someone may have inadvertently turned off the feature. Run 1-2-3 and enter a row of numbers in cells B1 through B5. Now choose File, User Setup, 1-2-3 Preferences. When the 1-2-3 Preferences dialog box opens, click the General tab. Select the check box labeled Use Total To Sum Automatically, then click OK to close the dialog box and save your new selection.

Move to cell A7 and type

Total

When you press the down arrow, the sum should appear in cell B7.

RELOCATING THE LOTUS 1-2-3 WORK FILES

"I would like to have Lotus 1-2-3 save all my files in a folder named 123 Files. I don't see a way to get Lotus 1-2-3 to save files anywhere besides the default folder. Is there a way to change this?"

You can save Lotus 1-2-3 files in any folder you wish. Run Lotus 1-2-3 and choose File, User Setup, 1-2-3 Preferences. When the 1- 2-3 Preferences dialog box opens, click the File Locations tab. Now delete the current entry in the Workbook Files entry box and instead type

c:\123 Files

Click OK to save your new selection and close the dialog box.

>From this point on, 1-2-3 saves (and looks for) its files in c:\123 Files.

PASTING CHARTS INTO WORD PRO DOCUMENTS

"I would like to paste a 1-2-3 chart into a Word Pro document. The problem is that I only need the chart, not the entire worksheet. When I open the worksheet containing the chart, select it , then choose Edit, Copy, I end up pasting the entire worksheet, chart and all, into Word Pro. Can you tell me what I'm doing wrong?"

What you are apparently doing is selecting the worksheet, not the chart. Open your worksheet and click once inside the chart. Now choose Edit, Copy, then move to Word Pro and choose Edit, Paste. This should paste in your chart only.

You can also right-click the chart and choose Copy in 1-2-3, then move to Word Pro and press Ctrl-V to paste in the chart.

NAVIGATING IN 1-2-3

"I sometimes have to use very large worksheets. Is there an easy way to get to the very last cell containing data? For example, if the last cell that has data in it is Y300, can I push a key to go to Y300 immediately?"

No, but you can press TWO keys and get there immediately. Just press End and then Home, and 1-2-3 navigates to the last cell containing data--Y300 in your example.

LEAP YEARS IN 1-2-3

"With all the talk about possible leap year problems, I decided to check up on 1-2-3. To my surprise, I discovered that 1-2-3 apparently considers the year 1900 a leap year. This is not true. A year is a leap year if divisible by 4, unless it is also divisible by 100. A year is a leap year if it is divisible by 400, which explains why 2000 is a leap year. So although 1900 is divisible by 4, it is also divisible by 100, and could not have been a leap year."

You can check this very easily. Just enter

2/29/1900

into any 1-2-3 cell and you'll see that it works just fine.

But if you enter

2/29/1901

1-2-3 will decline to display the date because it's invalid.

ENTERING DATA QUICKLY INTO SELECTED 1-2-3 CELLS

"Here is a 1-2-3 technique I heard about several months ago. When you need to enter data in a block of cells, select the cells first. Then, each time you enter data in a cell, press Enter. 1-2-3 enters the data and moves to the next cell. This continues until you have entered data (or at least pressed Enter) in all the selected cells. If you keep pressing Enter, you will simply cycle through all the selected cells."

FAST FORMATS IN 1-2-3

"Since I usually use borders and shades in my large 1-2-3 worksheets, I was looking for a way to transfer one type of shading and border quickly to another part of the worksheet. I happened across the Fast Format command.

Say you have exactly the cell format you want in cells B4 through D8; just click inside the formatted area and choose Range, Fast Format. The cursor turns to a brush icon. All you have to do now is drag the brush cursor around the area you want to format. When you finish, press Esc to turn off Fast Format."

AN INTERESTING 1-2-3 PROBLEM

"I ran into an interesting Date function problem while using @rate functions that depend on start and stop date time periods. I use Lotus1-2-3 97 in Windows 98. An example will demonstrate the problem.

"Place

@Date(99,12,31)

in cells A1, B1, and C1

then place

@Date (00,1,1)

in cells A2, B2, and C2. Format these date numbers to show the number in B1 and B2 and the date in C1 and C2. It will look rather like this:

@date(99,12,31)/36525/31-Dec-99
@date(00,1,1)/1/01-Jan-00

"Now add cells A1 and A2 in cell B4 to get number 36526 and copy and format it as a date in cell C4. The result is 36526/01-Jan-00.

"If you are looking for a difference between today's date and a date last year or earlier, to calculate a rate of return, the number will turn negative--date(00,1,1) minus @@DATE(99,12,31) will be 1 minus 36525; that's negative 36524 instead of positive.

"I have placed my current dates into neutral cells and added the cell containing @date(99,12,31) to get a revised date that works in my formulas as the formula current date. This must be the hard way. Is there a fix or download I am missing?"

It took us a while to figure this one out. What you have to do is use the number 100 to represent the year 2000. The numbers you can use range from 0 to 199. These numbers represent the years 1900 through 2099--so 100 is 2000.

CALCULATING WEEKDAYS IN 1-2-3

You can calculate the workdays between two dates using the @Networkdays function. Let's look at an example of how @Networkdays operates. Suppose you need to know how many workdays exist between March 1, 2000, and March 31, 2000. Enter the following into cell A1:

3/1/00

and enter this into cell A2:

3/31/00

Now move to cell C1 and enter this:

@networkdays (a1,a2)

Press the Down arrow. Cell C1 will display 23--the number of workdays between March 1, 2000, and March 31, 2000.

CALCULATING WORKDAYS IN 1-2-3

"I have been looking for a way to calculate the number of weekdays between two dates. For example, if I want to know how many workdays are in February 2000, I would like to enter 2/1/2000 and then 2/29/2000 and have 1-2-3 return the number of weekdays between the two dates. Can you do this in 1-2-3?"

1-2-3 has just the function you need--NetWorkdays. Let's see how it works. In cell A1, enter

2/1/2000

and in cell A2, type

2/29/2000

Now move to cell C1 and enter

@networkdays(a1, a2)

1-2-3 displays 21, the total number of weekdays in February 2000.

Let's suppose you aren't sure how many days are in a given month. You can modify the formula in C1 to

@networkdays(a1, a2)-1

and then change A2 to

3/1/2000

This method still produces 21--no one ever need know about that extra day in February.

CHANGING THE TAB KEY ACTION IN 1-2-3

"I recently began using 1-2-3 after using Excel for several years. In Excel, when you enter a number and press Tab, the cursor moves to the next cell to the right. When I do the same thing in 1-2-3, the cursor moves all the way to the right. If I enter a number in column A and press Tab, I end up in column K rather than column B.

"Another related problem is that pressing Enter seems to do nothing at all. I would prefer to have 1-2-3 move down one cell when I press Enter.

"Is there any way to get 1-2-3 to move the cursor only one cell when I press Tab? And can I tell 1-2-3 to move down one cell when I press Enter?"

Pressing Enter in 1-2-3 confirms the cell entry. If, for example, you enter an invalid formula, Enter leaves the cell open for editing.

It's fortunate that you want Enter to move the cursor down one cell and Tab to move it one cell right, because 1-2-3 groups these two actions. To change the defaults, choose File, User Setup, 1-2-3 Preferences. When the dialog box opens, click the Classic Keys tab. Now select the radio button labeled ''Tab moves right one cell, Enter confirms and moves down". Click OK to apply your selection and close the dialog box.

1-2-3 now moves the cursor one cell to the right when you press Tab, and one cell down when you press Enter.

CROPPING THE 1-2-3 MAP

"One 1-2-3 tip I have never seen is how to crop the map. Suppose you want a map of the United States showing only a few states. To crop the map to the needed states, click inside the map, then hold down the Ctrl key while you use the mouse to draw a rectangle around the states you want to display."

If you'd like to check this out, run 1-2-3 and choose Create, Map. Use the mouse to draw the rectangle that will house the map. When the Map Types dialog box opens, select USA By State and click OK. Now click the map and hold down Ctrl while you drag a rectangle around the states you want to show. When you release Ctrl, the states inside the rectangle appear in the frame. You can repeat the procedure to narrow down the view even more.

COLORING 1-2-3 TABS

"I often use 1-2-3 workbooks with 10 or 12 worksheets. I can keep track of things more easily if I set each tab to a different color. To do this in 1-2-3, click the tab you want to color and choose Sheet, Sheet Properties. When the Properties dialog box opens, click the Basics tab.

"Next, click the arrow at the right side of the list box labeled Tab Color. Choose a new color from the list. Leave the dialog box open and click a new tab. You may need to click it a second time to get the Tab Color list box. When you see the box, select a new color and move along to the next tab. After you finish setting your tab colors, close the dialog box by clicking the X in the upper right corner."

MORE COLORING IN 1-2-3

In the last tip, we described how to set the 1-2-3 tab colors. This time we discuss how to color the worksheet itself:

"I have always found it much easier to work with the green-and-white paper people refer to as computer paper. When I work with a complex sheet in 1-2-3, I see the entries more easily if I color every other row green.

"To color every other row, click one of the rows at the far left of the window (in the label section). Press and hold down the Ctrl key while you select the remaining alternating rows you want to color.

"After you finish selecting all the rows you want to color, choose Range, Range Properties. When the Properties dialog box opens, click the Color, Pattern, And Line Style tab. Click the arrow at the right side of the Background Color list box and select your color. If you want green, choose a light green; if you select a dark green, your data will be harder to see."

To close the dialog box, click the X in the upper right corner.

ENTERING TIME IN A 1-2-3 WORKSHEET

"How do you enter time into a cell in 1-2-3? I have tried several methods and all end up as text entries. I entered 11 AM, 1100 AM, and 11:20 AM. None of these seemed to work."

The last one should have worked. You should check that one again. The trick is to use a colon as the separator. You can enter time in the form 11:00 AM, 11:00 PM, 11:00, or 1:00 PM. Enter 13:00 if you want to use 24-hour time.

ADDING SHEETS TO A 1-2-3 WORKBOOK

"It looks like I will need to create some very large 1-2-3 workbooks. How many worksheets can I put in a single workbook? I don't want to keep adding sheets only to discover too late that I have hit the limit."

The chances are very good that you won't ever hit the limit. A Lotus 1-2-3 workbook can accommodate 256 worksheets.

For those of you who have never added a worksheet to one of your workbooks, choose Create, Sheet. When the Create Sheet dialog box opens, you can enter any number from 1 to 255, depending on how many worksheets you already have. If you have three, you can enter up to 253 more.

TRANSPOSING DATA IN 1-2-3

"Here is a 1-2-3 feature I ran across accidentally. I had already entered several columns of data complete with formulas, when my manager informed me that I needed to place the data in rows rather than columns. I looked for a way to get 1-2-3 to do this job for me. What I found is a command called Transpose. Transpose does almost exactly what I needed. I thought others might like to know about Transpose."

To see what  is happening, run 1-2-3 and enter

1

2

3

4

5

in cells A1 through A5.

Now select A1 through A5 and choose Range, Transpose. When the Transpose dialog box opens, accept the defaults for now and click OK. At this point, your data will appear in row A at A1 through E1.

You still have your original data column A. If you no longer want this data, you can select cells A2 through A5 and press Delete.

CHANGING 1-2-3 WORKSHEET FORMATS

"I most often work with fixed decimal numbers. When I use 1-2-3, I have to select a group of columns and then set the new format. Is there a way to change the number format for an entire worksheet rather than just the selected cells?"

You can change the number format for the entire worksheet. Run 1-2-3 and open a blank worksheet. Now choose Sheet, Sheet Properties. When the Properties dialog box opens, click the Number Format tab--its icon is the pound sign (#)--and select your format from the Category and Current Format list boxes. You can use the Decimals spin box to set the number of decimal places.

After you make all your selections, click the X in the upper right corner to close the dialog box.

WORKING WITH SPECIFIC NUMBERS IN 1-2-3

"I use Lotus 1-2-3 97 and have this problem. I have a column of numbers that looks as follows:

3
3
5
6
5
6
5

"Each number is in a row (D1=3, D2=3, D3=5, and so on). I have tried without success to write an @IF formula that will search for the occurrence of a specific number--say, 3--in column D and sum the values of every row to the left of it. The formula would ignore the other numbers in the column. If this is possible, could you or any of your subscribers help?"

We gave this a try and came up with a possible solution. Perhaps some other readers will have a better method. Here's what we suggest.

In column A, enter

5
2
3
5
6

and then, in cell C1, enter

@IF(A1=$E$1,$E$1,"")

and press Enter.

Now click cell E1 and enter 5, then press Enter. This formula displays the value entered in E1 if the value in A1 is equal to the value in E1. If the cells are not equal, the formula displays nothing.

Next, copy C1 down through C5. In C7, enter

@sum(c1..c5)

and press Enter to sum all the occurrences of the number entered in E1.

This method uses an extra column plus a cell. You only need the extra cell if you sometimes want to change the number you're searching for.

USING UNDO IN 1-2-3

"I think I have encountered a bug in 1-2-3. I recently made some changes to a worksheet and printed it. When I looked over the printout, I realized that I should not have made the change. I pressed Ctrl-Z and nothing happened. I had to change the data back to its original form manually. I have never had a problem with Undo before. Do you have any idea what might  have happened?"

Yes--you can't use Undo after you print the worksheet. There are several operations that clear the Undo buffer, and printing is one of them. The other operations that clear the Undo buffer are saving a file, running a macro, and running a script.

OPENING THE 1-2-3 INSERT DIALOG BOX

"It seems that everyone suggests choosing Range, Insert to open the Insert dialog box. There is another, faster way to open this dialog box--you can right-click the worksheet and choose Insert."

Yes, and you can open the Delete dialog box the same way. Just right-click and choose Delete.

If you want to insert a new row, right-click where you want to insert the row and choose Insert. When the Insert dialog box opens, select the Rows radio button and click OK to close the dialog box and insert the new row.

SELECTING MULTIPLE RANGES IN 1-2-3

"This may sound odd, but I need to select an entire row starting at cell A3 and then select the entire column starting at E1. I need to have both selected at the same time. Is this possible in 1-2-3?"

Yes, it is. You can click the label area (at A3, click the 3) to select your row. Now press Ctrl and click the label area (click the E) to select your column. This leaves you with both the entire row at A3 and the entire column at E1 selected.

SORTING BLANK CELLS IN 1-2-3

Lotus 1-2-3 offers two ways to sort blank cells in a range. You can tell 1-2-3 to move all the blank cells to the bottom of the range, or you can move all the blank cells to the top of the range.

To see how this works, enter the following into cells A1 through A8:

1

2

3

4

5

6

and choose Range, Sort. When the Sort dialog box opens, click OK. If the blanks sort to the bottom and you would prefer to have them sort to the top, choose File, User Setup, 1-2-3 Preferences. When the 1-2-3 Preferences dialog box opens, click the General tab (if necessary) and deselect the Sort Blank Cells To Bottom check box. Click OK to close the dialog box and store your setting.

NAVIGATING IN 1-2-3

"A short time ago, I was fooling around with some key combinations to see what might happen. I discovered that you can press Ctrl-right arrow to move ten columns to the right. For example, if you are in column A, pressing Ctrl-right arrow will take you to column K. If you are in column B, ctrl-right Arrow moves you to column L. You can move ten columns to the left with Ctrl-left arrow."

SPRUCING UP 1-2-3 CHART COLORS

When you present 1-2-3 charts, you aren't stuck with the default chart colors. Let's suppose that you're creating a column chart with two data sets. By default, you'll get two solid colors (ours are red and green). But you can change the colors very easily. Just double-click one of the columns to open the Properties dialog box. When the dialog box opens, click the Color, Pattern, And Line Style tab.

Click the arrow at the right side of the Pattern Color list box and select a new color. You can also click the arrow at the right side of the Pattern list box and select a new pattern. After you select the pattern, click the arrow at the right side of the Background list box and select a new color.

As you make these changes, keep an eye on the chart to observe the effect. If the Properties dialog box is over the chart, just grab it by the title bar and move it out of the way.

Note that several patterns offer a blending effect. As an example, you could choose a light green as your pattern color, a dark green as the background color, and one of the blending patterns to achieve a very nice color blend for your columns.

After you make all your selections, you can click the Close box to dismiss the Properties dialog box.

HIDING CELLS IN 1-2-3

"I am developing a 1-2-3 worksheet that uses several cells to hold interim calculations. I usually put these cells out of sight, away from the primary area of the worksheet. However, I would really prefer to make these cells invisible to other users. Can you make a few cells invisible and still use them for calculations?"

You can't actually make the cells invisible, but you can hide the data in those cells. To see how this works, in cells A1 through A3 enter the following:

5

5

5

Now, enter in cell A5

@sum(a1..a3)

1-2-3 will now display

15

in cell A5.

Let's hide the data in cell A1 now. Click A1 and choose Range, Range Properties. When the Properties dialog box opens, click the Security tab (its icon is a key). Select the check box labeled Hide Cell Contents. Since you want to allow changes to the cell, deselect the ''Protect cell contents from changes" check box and then close the dialog box (click the X in the upper right corner).

The 5 in cell A1 will disappear, but the sum in cell A5 will remain 15. If you like, you can change the number in cell A1. Whatever you enter will disappear as soon as you press Enter or an arrow key.

CALCULATION ACCURACY IN 1-2-3

We recently talked about calculation accuracy in 1-2-3. We discussed only addition. Here is more information:

"You stated that there was no problem calculating multiple decimal places in 1-2-3, but the example you used was addition. You are correct in terms of addition and subtraction, but incorrect relating to multiplication and division, when you must use @round in order to do a cross-footing on a worksheet that contains multiplication or division within the cells. Because the default is about 10 decimal places, the totals will be off horizontally from vertically."

When you use multiplication and division, you should use the @ROUND function.

To use @ROUND, enter

@round (cell id, number of decimal places)

Questions?

Just Check out some of our sponsors

Shop at BestPrices.Com!

COPYRIGHT 1998 - 2008 All names used are Trademarks of the respective companies

[ Home ] [ Up ]

Send mail to CompanyWebmaster  with questions or comments about this web site.
Copyright © 2007 HALO Computer Technology
Last modified: 02/14/08