h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

HELLLLPPP!!!


Ok, how the heck do I do the following in Excel...

Column 1 is straightforward, a list of document titles
Column 2 is the number of words in each document
Across the top of the page starting from column 3 to 6 are names of translators

What I need is a checkbox in columns three four, five and six, so that one (and only one) of the four columns can be selected, thus linking a single document directly to a single translator...

Then comes the even worse bit... I need to be able to see (anywhere on the page... a list of the translators with the number of words they've translated.

Anyone anywhere any idea if this is possible under Excel 2000

OK, self referencing cells aren't possible in Excel so to ensure only one column is selected in each row you'll need a formula for the check box which relates to another box for entries. This will need an additional column. Sumif can be used to add up the number of words.

Like this:
assuming you start in cell A1

Title // Words // Translator1 // Translator2 // Translator3 // Translator4 // Choice
(these may not line up - sorry)
German Humour // 5 // IF(G2=1,1,0) // IF(G2=2,1,0) // IF(G2=3,1,0) // IF(G2=4,1,0) // 2

This should put a 2 in cell G2 and a 1 in cell D2 denoting translator2. The foot of each translator column reads SUMIF(C2:C10,1,B2:B10) Assuming ten rows of titles. The C in the formula will change with each column of course.

Any use?

F

If you use 1's and 0's instead of ticks and absences of tick, then you can have columns 7 - 10 with the 1's and 0's multiplied by the number of words. So if there is a 1 in column 3, the number of words in that document appears in column 7. THen all you need to do is to add up the columns.

That's probably the way I'd have gone about it Fathom. The only way to do checkboxes would be to use VBA, and that would probably just complicate matters.

Hmm, took me about ten minutes to figure out what you were going on about there fathom, but I think I've got it...

Having said that, Gnomon's idea does have the advantage of being simple!

think what you are looking for would be easier with a database such as access

Don't I know it!

Unfortunately, we don't have a copy of Access at work here!

to rub it in!

Couldn't you download OpenOffice and use the database package included in that? http://www.openoffice.org/product/dbase.html

(Oh and if you want to improve in Gnomons solution you could add in an extra column containing the sum of 1s and 0s in the row and use data|validation to prevent more than one 1 in a row)