gaily flashing lites

Christmas Shopping List meets Spreadsheet by Cindy O'Hora
"Oh Christmas list, Oh Christmas list, how easy you make shopping." J O'Hora (Isn't my daughter clever! I wonder where she gets it?)

Every year there was a light hearted moment at Christmas. We had a relative who gave each of my brothers the same gift. At Christmas, we all "guessed" the gift before it was opened. When I spent my first Christmas with my husband's family, I discovered ours was not a unique experience.

I decided then and there that I would not be the aunt of the next generation who perpetuated this tradition. As our family circle grew, it became more difficult to remember what I had given all these beloved folks, in the past.

Being a techie, I applied my Mac and AppleWorks to this challenge.

Open a new AppleWorks Spreadsheet

Resize:

By default, AppleWorks spreadsheets are 40 columns wide and 500 rows long.
Heaven help you if you need that much space!

Make this spreadsheet smaller.

1. Go Format .. Document. The Document dialog box will open.

2. Under Size - reset the columns to 10 and the rows to 50. If you need more, adjust accordingly.

3. Click OK

The spreadsheet document should be a much more manageable size now.

Data Entry

1. Click in cell A1 type - Name in the entry bar. While the A1 cell is still highlighted, Go .. b. That makes the text bold.

When you type data in the entry bar (see arrow in illustration at the right), it is entered in the selected (highlighted) cell when you click away from the cell. OR you can click the check mark to see the data appear in the cell.

 

Cells are identified by their Column letter and row number. The highlighted cell is A1.

2. Down Column A - list all your family members. Each person gets their own cell. Skip a cell between family units.

Quick entry tip: After you enter the name, hit the return key. The data is entered in the cell and you move down the column 1 cell row.

This is much faster than mouse clicking your way down.

Save. ..s.

If you need to change something entered in a cell:

Click in the cell. You will see the contents appears in the Entry Bar at the top of the spreadsheet. Change the data in entry bar and click the check mark.

3. Click in cell B1 enter - Gift. Go .. b.

In Column B, I entered what I gave them. I made this column wider.

1. Click in cell B1.

2. Go Format .. Column width. Enter 150.

Notice that all the cells down that column adjusted to be wider. All the cells in a column are the same width. You cannot vary the width from one cell to another within the same column.

Nor can you vary the height of cells within a row. Set a height for one, it is the height for all.

spreasheet cell example
You can also adjust the column width by using the column heading cells.

1. Move the cursor over the line between two column headings. Notice that it changes appearance to a vertical bar with two arrows.

2. Drag the mouse right to increase the column width. Go left to decrease it. Go ahead and play with this a bit. I'll wait.

4. Click in C1. Enter - Cost. Format it to be bold.

Cost means money. I formatted these cells for currency.

1. Beginning in cell C2 - select all the cells down the column. Drag down until they are all highlighted.

2. Go Format .. Number (see illustration at right)

3. Change the Number pop up menu to Currency.

4. Click OK.

This only gets really exciting when you try it.

Enter 20 in Cell C2. Hit the Enter Key on your keypad.

Ta da! AppleWorks added a dollar sign and a decimal point with two zeros - yielding $20.00

If you need to enter a cost that includes cents:

Use the keypad to enter 30.5 Hit the Entry key. AppleWorks will convert it to $30.50

Pretty spiffy, don't you think? Of course, you can do this all by hand. But why?

Save. ..s.

Number Format dialog box

Format Number, Date, Time dialog box.

 

 

 

What is this all adding up to?

I use the SUM function to total my costs. The SUM function adds all the numbers in a selected set of cells. You tell the function which cells to total.

1. In Column C - click in the cell one row lower than the last name entered. In the illustration at the right it is cell C6.

2. In the Entry Bar type =SUM(

3. Click drag the mouse from cell C5 to cell C2.

4. Look at the entry bar. The formula should now read =SUM(C5..C2

5. Finish the function's formula with a close parenthesis. )

It should now read =SUM(C5..C2)

Click the check mark. If the formula is written correctly, you get a total for all the numbers selected.

Two common errors when creating this formula:

1. Forgetting to add the ) close parenthesis.

2. You include the cell you are utilizing to total in the formula. In my example, that would be C6. The formula would read =SUM(C6..C2). AW will error and inform you there is a circular reference.

When this happens, check the addresses of the cells you added to the formula. I can easily correct this by changing the C6 to C5 in the Entry Bar.

Save. ..s.

SUM function example

SUM Function illustration. Note cell C6 is highlighted the function is being entered there.

Harnessing a header:

Label your spreadsheet by adding a header.

Go Format .. Insert header.

Notice that the menu bar changes to the word processing environment.

Enter Christmas shopping list

Save. ..s.

Remove the Headings:

Although they are very useful while creating your spreadsheet, the column and row headings are acutely unattractive when printed.

To remove them:

1. Click in any cell.

2. Go Options .. Display

3. Unclick Column heading and Row heading.

Click OK.

Print the spreadsheet. To save ink and paper....

Highlight the rows and columns you have data in.

Go to the last empty cell in Column C.

Drag highlight back to cell A1.

Now print.

Each time you make a purchase, add it by hand to your paper pocket list. When you get home, update the list in the computer. You may be surprised as the numbers add up to a big gift bill. You may find this handy Gifts that cost nothing. Great Gifts for kids your time & local adventures

The next year

I shop for the holidays throughout the year. I update the gift list, in February.

I highlight all the people in Column A. Go Edit ... Copy. (command key..c)

I highlight Column E and Go Edit ... Paste. (command key... v.) This transfers all the names to the next year's column without typing them all over again. (My Copy...Paste evangelism!)

I skipped one column for the gifts and again created a cost / SUM total column.

I set the Page Setup to Landscape (sideways) and print my spreadsheet. I stick it in my purse.

As I shop, I fill in the appropriate gift and cost boxes (cells). I am confident I am not repeating gifts. (I am also avoiding buying two gifts for anyone. Not, that I've ever have. Oh no, pas moi!)

Onward and upward!

The next year I do all this again on the same SS so that I now had a two year record in my purse. Which brings us to my daughter and I having lunch, at the mall, on Black Friday. We had our heads together consulting the "sacred list" for the folks we still needed to "gift".

A friendly lady at the next table leaned over and said, "What a great idea!"

I smiled, thanked her. I went home and updated my computer list. Nothing like a total stranger's affirmation to buoy your enthusiasm for a well made plan!

By the By - I also use this to track books I have donated to the district's libraries. I have adopted the computer books section at the high school. This is the only way I can avoid duplicating now that I have given over 70 books.

May everyone be blessed this holiday season with the greatest gifts of all - peace and love. Cindy O'Hora

holly line

Internet Hunts / Nature / Computers / Pennsylvanians / Puzzles & Projects / Site map / Home

All trademarks, copyright and logos belong to their respective owners.
©1999 Cynthia O'Hora All Rights Reserved. Updated 10/2008
Cindy's email address

This site is an entirely volunteer effort. I am not associated with Apple Computers or any of its subsidiaries.

 Made with a Macintosh