Help me figure out what kind of software I'm looking for!

[doctorhook]

Hey team! Computer Use is a cross-class skill for me, so I need a little bit of help. I'm looking for a program; I know what I want this program to be able to do, but not what sort of program to look for. Let me explain.

I'm trying to create an index of second language vocabulary for my own use in studying. (If anyone's curious, the language is German.) So far, I've been using Microsoft Excel 2003 to create lists, and although I'm aware Excel can be made to perform some of the functions I'm looking for, my Excel-fu is definitely weak.

Essentially, I want to be able to enter vocabulary data into a program and then be able to access that data in a variety of ways. To summarize, I want to be able to:

• Index, display, and print entries in three columns: A column of German words, another column for additional forms of the word, and a third column for each word's English translation;
• Customizable font effects (Red for feminine words, bold for verbs, and so on), ideally automatic;
• Self-alphabetizing!;
• Sortable according to a variety of parameters, specifically: Alphabetically in German, alphabetically in English, by word type (noun, verb, adjective, etc.), by word gender, and by chapter, ideally by simply clicking on a column heading;
• Customizable data! I add information to the program, and it sorts it out for me based upon what I tell it, so that I have an index exclusively made up of data which I have inputted;
• Selectable data! I can choose to print the entire index (sorted however I want), or I can choose to only print words from Chapter 3, or Chapters 1 through 5, or only part of Chapter 7, for example.

Obviously I'm asking for a lot of functionality and customizability from a program. Like I said, I'm no pro with Excel, but I can't shake the feeling that what I'm looking for will end up being some kind of spreadsheet program (perhaps the one I'm already using). If that turns out to be the case, you're going to have to teach me/help me learn to make it do what I want it to do.

Any help you guys and girls can offer would be greatly appreciated! Thanks!

Sincerely,
- Kyle (aka doctorhook)

 

[Merkuri]

Excel can do all of that if you spend a little more time with it. I don't remember where all of the options are (I've moved onto Excel 2007, which is significantly different, and I've forgotten where things are in the Excel 2003 menu) but if you turn on auto-filtering you can easily sort your data by whatever column you want (it's not auto-sorting, but you can add a piece of data at the end and re-sort it by the first column), and you can use conditional formatting to format some of your data based on whatever criteria you want.

I attached an example. I made it in 2007, but saved it in 2003 format. You can click on any of the dropdown arrows on the headers to sort or filter it by whatever criteria you want. Once you have it filtered the way you like you can print it and it should only print what you have showing. I also applied the conditional filters you used in your example. As an added bonus I froze the header row in place so that if you add a lot of words and have to scroll down you can still see the headers.

Excel is very powerful once you learn your way around it. I threw that sheet together in about five minutes, but I use Excel every day so I'm very comfortable with it and know what it can and can't do.

 

[doctorhook]

Excel can do all of that if you spend a little more time with it. I don't remember where all of the options are (I've moved onto Excel 2007, which is significantly different, and I've forgotten where things are in the Excel 2003 menu) but if you turn on auto-filtering you can easily sort your data by whatever column you want (it's not auto-sorting, but you can add a piece of data at the end and re-sort it by the first column), and you can use conditional formatting to format some of your data based on whatever criteria you want.

I attached an example. I made it in 2007, but saved it in 2003 format. You can click on any of the dropdown arrows on the headers to sort or filter it by whatever criteria you want. Once you have it filtered the way you like you can print it and it should only print what you have showing. I also applied the conditional filters you used in your example. As an added bonus I froze the header row in place so that if you add a lot of words and have to scroll down you can still see the headers.

Excel is very powerful once you learn your way around it. I threw that sheet together in about five minutes, but I use Excel every day so I'm very comfortable with it and know what it can and can't do.

You are a god among internauts, Merkuri! This will put me leagues ahead of where I'd have been without it! You rock! And thanks!

You've gotta show me exactly how this is done so I can do it for myself in the future, or alternatively, direct me to a solid reference for it! Also, would you recommend I upgrade to Excel 2007? FWIW, I use Excel on a fairly regular basis, but generally only for really basic things. (I'd probably use it for more if I knew how to! )

Anyway, thanks again Merkuri!

 

[Merkuri]

I wouldn't actually recommend upgrading to Excel 2007 if you only use it for basic things. The biggest difference your average user will see in Excel 2007 is the interface, and its WAY different from 2003. If you do decide to upgrade be prepared to take a few weeks (if not months) to get used to where everything is.

Really the main reason I have 2007 is because that's what they gave me at work, and when I went to go by a version for my home machine I decided it was best to have the same version on both home and work machines. I could've lived with the version I had at home (Excel 2000) except it wasn't quite a legal copy and when I was no longer a poor college student my conscience wouldn't let me keep using it.

The features I used were "auto filter" (or just "filter"), "conditional formatting" and "freeze panes". Auto filtering and freezing panes are easy to use once you've found them in the menu (and I can't help you there... I'd need to look at a copy of Excel 2003 to find it).

For the auto filter just highlight the top row (where your column headers are) and click the auto filter menu item. It'll add the dropdown arrows. If you add more columns later just select the top row again and hit that menu option again (you may need to click it twice: once will remove the old arrows and again will add them back, including the new columns).

For the freeze panes, highlight the row UNDER where you want it to freeze (in my case I highlighted row 2) and click the menu option. The rows above where you highlighted will be frozen in place and when you scroll up and down they'll stay in place. Alternatively you can highlight just one cell and it'll freeze the rows above that cell and the columns to the left of it, so if you end up with a lot of columns and need to start scrolling left and right you can select cell B2, click the freeze panes option, and you'll always have your first column and first row visible no matter where you scroll.

The conditional formatting is a little harder, and it changed a bit from 2003 to 2007 so I can't give you a step-by-step on how it's done. Basically you select a cell or a group of cells and click the conditional formatting option and it'll ask you for a formula and the format you want. When that formula is true it'll apply the formatting to those cells. The hard part here is figuring out what formula to use. The EXACT() function is handy here.

To apply formatting to a whole row based on the value of one cell in that row I usually do the following:

1) Select one row
2) Use this type of formula:

=EXACT(C$3, "Value")

In that example, C is the column I'm checking, 3 is the row I highlighted, and "Value" is the text I'm looking for in that cell. The $ behind a row or column means that item won't change. C$3 means it'll always check column C but it might check different rows.
3) Pick the formatting you want and look to see that it got added to your row correctly.
4) Highlight that same row again (if it got un-highlighted), click the "format painter" button, then apply that format to all of your rows.

It's possible to select all of your data at once and apply the formatting in one fell swoop, but it's hard to figure out what to use for your formula in that case (do you use the last row number, or the first row number, or something else?) so I like to apply it to one row, make sure it works, then use the format painter to apply it to all of the rows.

I think in Excel 2003 you can only apply three or four conditional formats to a given cell. One advantage to Excel 2007 is that you can have more conditional formats. If you find yourself liking that feature and wish you had more than three conditional formats that might be a good reason to upgrade.

Another reason you might want to upgrade is that in Excel 2003 the "auto filter" dropdown menu lets you pick one item to filter on, and if you want to filter on more than one item (for example, if you want to see "Verbs" and "Nouns" at the same time) you have to write a formula. In Excel 2007 the dropdown menu gives you checkboxes for each item, so you can filter on as many as you want without having to write formulas.