Select a "column" of text in Word

I'm currently working rather far from home, which means a long commute each day and a lot less time. I'll get back to the gradebook soon. In the meantime I'll focus on quicker tips I can post.
You know how to select text horizontally. Did you know you can also select text vertically? (For example, if you want to select just the numbers in a numbered list.) Just hold down the Alt key while selecting.

Creating a Spreadsheet Based Gradebook, Part 2

Okay. Now it's time to make the second tab, where we enter all the information on the students. I'm going to sort the students by last name, so I keep first, last and middle name in separate columns. I also need to track the students ID (so I can post grades without names), and I want to keep all contact information here. So here are the columns I track:

• Last


• First


• Middle


• ID


• Email


• Phone

Here's how my Students tab looks so far:

And here it is with some "data" added.

It's very hard to read like this, so I want to use some conditional formatting to make every other line light green. Since the number of students I have each term varies, I want the row to stay white if there isn't a student's name in the first column.
Start by creating a stylesheet. Press F11 (or select Format|Styles and Formatting) to display the Styles and Formatting window. Right click in the window and select New. Set the formatting how you like. In this case, I changed the Background to Pale green, and named the stylesheet palegreen.
Click and drag to select cells A3 through F3 (the first row of data). Select Format|Conditional Formatting. In the Conditional Formatting window, change the Condition1 pull down menu to "Formula is" and enter the formula
AND(MOD(ROW();2)=0;ISTEXT($A3))
Change the cell style to palegreen (or whatever style you created earlier).
You saw the MOD(ROW();2)=0 in an earlier post. It is true for all even numbered rows. The second part of the formula, ISTEXT($A3)) is true if there is a student's name in cell A3.

Click OK.
Now, press Ctrl-C (or select Edit|Copy) to copy the selected cells.
Highlight cells A4 through F49 or so. Then select Edit|Paste Special. Deselect all of the options except formatting, then click OK to copy the conditional formatting into these cells.
Here's what you have:

Next, we'll start on the tabs to enter the different elements and grades.

Creating a Spreadsheet Based Gradebook, Part 1

I wanted to put together a spreadsheet based gradebook in OpenOffice that was easy to use, and didn't require me to copy and paste information over and over. Here's what I wanted:

1. All the information on the class in one spot (what the grading scale was, what the students would be graded on, and how the different pieces would be weighted.)


2. On another page I wanted the student list, including all the info I needed for them (student ID, phone numbers, email address.)


3. A page for each thing students would be graded on where I could enter their scores.


4. A flexible setup that would make it easy to add or remove projects, quizzes, and so on.

Part One: The Setup Tab

I created an OpenOffice spreadsheet and named the first tab Setup. This is where I'm going to enter the information about the course itself. I decided to make it easy on myself and any one else using the spreadsheet, and color cells that need input from the user. You might want to lock the other cells.
For the grading scale, enter the lowest grade a student can get to receive that grade. For example, if an A needs a 93, enter 93 in the field next to A.
Under Elements, enter the things that students will actually be graded on, and the weight of that element. We will be making a tab for each element later.
Next time: Student information.

Lookup Tables in Open Office, Part 1

My last entry showed how to pull the two-character state out of addresses in an Open Office spreadsheet. The problem is, you can't sort effectively on those. (Arizona is AZ, and Arkansas is AR. Sort them and you'll get Arkansas before Arizona ...) Another problem, you may want to show the state name as a heading and not the code. The solution is to add a lookup table to replace the state code with the state name. Here goes:
Start by adding the state codes and names in an unobtrusive place in your spreadsheet. I like to put lookup data on a separate tab. That way, there is less chance of accidentally changing the data.

Then, return to the previous tab, add a new column, and enter the lookup formula:
=LOOKUP(D5;StateLookup.B4:B62;StateLookup.A4:A62)
Now you can sort on your new column.

Splitting Cells in Open Office Spreadsheets Part I: MID

We recently needed to do a member list, sorted by state. We had gotten the addresses from someone else, entered into Excel. I easily moved the file into Open Office, but we ran into a problem with the addresses. The previous list owner had entered the city, state, and zip code into a single cell:
Willis, MI 48191
This meant that we couldn't easily sort the addresses by state. Rather than spend time retyping the state into a new cell that we could sort on, I decided to get creative and come up with a method that would get this info out for me easily. (I am, after all, lazy. :o)

The solution is to use the MID function. The MID function syntax is MID(text; start; number). So MID("It's a Wonderful Life";8;6) returns Wonder.

It turned out to be pretty easy, because the user always put a comma before the city, and the state was always a two letter abbreviation. That meant we just needed to find the location of the comma, skip the space, and take the next two letters for the state.

If the City, State, and Zip Code are stored in cell C2, we can use the following formula in cell D2 to pull out the two letter state:
MID(C2;FIND(",";C2)+2;2)
A translation of this formula: We are going to pull letters out of the middle of the string in cell C2. First, find the "," in the string. Then count two characters over from that point (the comma counts as the first character, so the comma and the space give you the +2 in the equation.) Then, take the next two characters. The result is the state.
But what if the user didn't put a comma in there?

It's still not a problem as the zip code is always 5 characters. Then you just need to use the following formula:
MID(C3;LEN(C3)-7;2)
Translation: We're going to pull two characters from the middle of the string in cell C3. The leftmost character we're going to pull out is at the length of the string minus 7. This is rather confusing. It looks at first like it should be the length of the string minus 8 (the 5 characters of the Zip Code, plus one space, plus the two characters of the State code). However, the length of the contents is the position of the last character. This means that to find the position of the start of the zip code we would only count back four characters, and so on.
The worst case, what if the data was entered in a haphazard method> Sometimes there is a comma, sometimes not. Sometimes there is a 5 digit Zip Code, sometimes not.

Here, you might want to start by cleaning your data. :o) If that doesn't work, you can still find the State code with a variant of the second equation. We just need to start by determining if the Zip Code is 5 or 9 characters long. We can do this first by checking to see if there is a hyphen in the Zip Code. If MID(C3;LEN(C3)-4;1) returns a hyphen, we know it's a 9 digit zip code. If it's not a hyphen, we have a 5 digit zip code. Then all we have to do is modify the extract script to pull the right two characters based on the type of zip code we find. Here's the final formula:
=IF(MID(C5;LEN(C5)-4;1)="-";MID(C5;LEN(C5)-12;2);MID(C5;LEN(C5)-7;2))

Better Google Searching

I use Google to search. I love that it's so streamlined and effective, and that the page is so uncluttered. What I didn't know is how powerful it is. I'm sure you know the basics, like how to search for multiple terms or exact phrases. But I just found out that you can use Google as a calculator. (Type 2400/60= in the Google search box to get the answer.) Or you can search just one site by adding site: (for example, search for all references to Circa on this site by entering circa site:templatesandtools.blogspot.com/ in the Google search box.)
Check out the Google Search Cheatsheet for even more ideas!

tracking your books

I haven't posted in ages. I took on a simple task (not!) to catalog my personal library, and it's taken a great deal longer than I expected to finish up. I've been meaning to do it for ages--if anything happens I want to be able to prove to my insurance company how much my library was worth. And I want to avoid buying multiple copies of books. And all those other great reasons for cataloging a library. The problem is, I own a lot of books. Really a lot. (I read my books more than once, and I like to actually own books.) So I started several times in the past, and gave up discouraged at the amount of time it took to enter the information for each book.
So this time I decided to start by researching my options. And I found a tool that makes it practical (if not completely easy) to catalog all my books.
Readerware, by Readerware Corporation, is a database that simplifies the data entry process. You just enter the ISBN and the program looks the number up online (you select where to look, from options such as Amazon and the Library of Congress) to locate all the required information about the book, including value, title, author, etc.
And, to make it even easier (yeah!), you can use a barcode reader to simply scan in the ISBN information. I ordered the version with the free cuecat bar code reader, and find I can scan in an entire box of paperbacks (about 40) in only a few minutes. It takes a little longer for the look up process, but I can leave and do something else while the application is working.
If you don't want to take this approach, you can also drag web pages (such as Amazon) to the database to add specific books. You can even manually add information for books.
The database provides several valuable reports and summaries, such as the number of books you own and their value. The database has some powerful searching and export options.
Finally, you can also get an Audio and Video version, for cataloging your movies and music. I haven't really worked with these much, but expect they work just as well as the book version.
The application is a little more expensive than others, but I found the saved time to be well worth the cost.
Readerware