Any EXCEL experts out there?

[stewart]

Hi
I'm trying to save myself some time keeping track of the number of detentions pupils in my year group get.
I've set up an excel spreadsheet with 2 worksheets. The first worksheet just lists the names of pupils who have been given a detention for a particular day. Like the picture here:

I want to keep a total on the second sheet for each pupil of the number of detentions they have been given. So in the screen shot below what I want to know is what formula do I put to give the total for John Smith in cell C4?

I've tried to find the answer myself in Help but with no avail.

If anyone can help I would be really grateful. Of course, I could also use the same technique to keep track of how many good things have happened to pupils too

Cheers
Stewart

 

[tim]

Stewart

No images showing!

Cheers
Tim

 

[Kane]

there's a function in Excel that counts non blank cells in a range :- =COUNT(RANGE)

So assuming you're going to put the dates of his detentions in row 3 starting at G3 you could use =COUNT(G3:IV3) IV3 being as far to the right as you can go and the result will count the cells which are populated with, in this example, the date. Be aware that if you type anything else into the range it will count that as a detention as well.

Si

edit - odd I could see the pics when I first viewed the post ...

edit 2 - forgot to say that you'd have to specify the sheet as well, in this case =COUNT(SHEET1!G3:IV3)

 

[Alf]

The images are on Geocities and guess what? The bandwidth allocation has already been maxed out. ](*,)

Cheers, Alf

 

[stewart]

Have now moved the pictures from Geocities - hope they can now be seen ok.
thanks for the responses so far. Kane, I'm not sure I have explained myself properly so your suggestion wouldn't help me.
Each day I get access to a list of names of pupils with detentions. I add this to my excel spreadsheet on Sheet 1 so that I build up a chronological list of detentions.

On Sheet 2 I want to be able to keep a total of the number of detentions each pupil has received so that I can keep a close eye on it - some of them can quickly rack up dozens!

So I want to have a way that Column C on Sheet 2 can update itself with each pupil's total number of detentions.

Hope this makes it clearer!

Cheers
Stewart

 

[matt]

So you need to count the total number of occurances of each name. To start with you'll probably benefit from joining the entries in column B & C in to a single string (to avoid mixing your "smiths" or, to put it another way, increase the uniqueness of the name), then count the occurences of each unique name string.

Just thinking beyond your most immediate need... Might you want to measure number of detentions in a given time period (say the last 3 months) plus the total? Just a thought...

I've not got Excel on my home PC so can't really explore. However, I'd start with functions, although you're bound to need to nest one (to concatanate the names) within the one to do the count.

I'll try and have a quick look at Open Office and Lotus 123 to see if the solution is apparant.

 

[paulm]

Hi Stewart,

Can't think of a formula or combination of formulas that will do what you want, however you can do it by creating a pivot table on your second sheet.

This is a bit tricky to explain, but not so difficult to do.

Go to the Data command on the toolbar and select Pivot Table and Pivot Table Chart. This will bring up a wizard that takes you through some simple steps.

Rather than try to explain in detail have a read about it in the Help section and then try using the Wizard. If you get stuck I'll try and help but it might be tomorrow now as I'm knackered and off for a wee dram and an early night, tgif ! :lol:

Good luck

 

[stewart]

Thanks, Matt
Understood some of what you wrote!
I'll continue searching through Help as well.
Thanks
Stewart

 

[matt]

Doh... Been a complete idiot...

You'll probably still want to concatanate the first and last name but then use a Pivottable in your second sheet to give you the information that you want.

Couldn't see the wood for the trees!

I've done it in Open Office. PM me with your email address if you'd like me to send it to you.

(Just noticed your post Chisel - you beat me to it)

 

[Anonymous]

Stewart

Pivot tables are a great way to achieve what you are doing but there are a couple of other methods that will show similar results and may offer food for thought. Of course, it dosent hurt to have alternatives!

As Matt says you will have to ensure that you have unique names, and as suggested you could concatenate the forename and surname in column c and shift everything else along one column. Alternatively do the kids have a unique number that you could use?


The first method that I thought of was to use the "countif" function which will count the number of occurances of specific text with in a range. this is the closest that you will get to a single function to use.

The other method i thought which is slightly different but will offer you greater analysis (if you want it). The auto filter function will allow you to filter a list using various different criteria, for example forename and date. Again its worth checking out and is very simple to use. Coupled with this are a set of subtotal functions that allow you to sum, count, max, etc on a filtered list which work in the same manner as the normal functions.

Look them up in the help files and if you have any specific questions don't hesitate to ask.

Saint

 

[Waka]

Stewart

Try the following it should sort it out for you:

Click on the box you want the result to go into
Insert the equals sign
Go to spreadsheet 1 and click on the box with the information you want to show on the second spreadsheet, if its more than one box add the plus sign between each box
Go back to the 2nd spreadsheet and press enter, data will now be entered automatically on the 2nd spreadsheet from the information you insert on the first.

You could always send me a dummy spreadsheet and I will insert the calcs for you, then you'll be able to see whats what. if you want to do this then send the sheet to [email protected]

 

[paulm]

Nice one Saint, had forgotten about the countif function !

That is the quickest and easiest way to set it up initially. However, as new names are added to the list it would mean updating the formulas accordingly, not a big chore admittedly, but would need ongoing maintenance.

While the pivot table might take a little longer to set up initially, providing you make the source range it applies to much larger than the present list of names, as you add new names into the list the pivot table will pick them up automatically without any further work/editing being required, just select the refresh option and it's self updating. :wink:

As always, more than one way of doing most things :lol:

 

[Anonymous]

Chisel

I wasn't sure if the pivot table would automatically adjust the range to accomodate new data, but like you say if you make the range big enough it will be the best solution.

Just a thought, use a named range for the data on sheet 1 and reference that named range in the pivot table wizard. The range will always be updated in the pivot table so long as you insert a line in the range when entering new data. You could even create a little macro to insert the line for each entry. Does that make sense? Who knows!!!

Saint
Excel Overengineerist

 

[Nick W]

If you are asking how to reference cells in one sheet from another then the answer is to use the "sheetname!" format. E.g. Sheet1!A1:A7

 

[stewart]

Thanks to everyone who helped. I can now use the countif function! well, most of the time.
I have a slightly different question now and wonder if there is any one who can say, aha, that's easy.... of course it might just be that it can't be done...

Is there anyway to do the following:
In the screenshot below I want to be able to take the name of a pupil who has had a missed detention and the total number of missed detentions and then display this on a different worksheet.

I know that I can use autofilters to look up the number of missed detentions for a pupil but I want to have a sheet that shows this automatically for only the pupils who have missed detentions.

Cheers
Stewart

 

[paulm]

Pivot table again I think Stewart !

Make sure that in the detentions missed column you just have blank cells or positive values rather than zeroes and then I think your pivot table will just show those with a positive value, though not certain about that.

Well worth spending 10minutes with the wizard and/or help screens and getting to grips with the pivot tables for the kind of stuff you are trying to do.

Happy to try and help with any points of detail when you get underway.

Good luck !

 

[stewart]

Thanks, Chisel.
Have to say that at the moment pivot tables are a complete mystery to me. However, I have found a way that does what I want using advanced filter and a macro (my first one ). On the worksheet where I have the missed detention i just have to press CTRL + U and the list is updated with the names of pupils who have missed detentions and how many they have missed.
Ideally I would like to have the information sorted by number of detentions with the most at the top. Still working on that...perhaps another macro?
Anyway it's been fun!
Cheers
Stewart

 

[Anonymous]

Just to add another possible dimension, have you considered MS Access? Its not so intuitive as excel but when you get into it its a really powerfull tool.

Saint

 

[stewart]

I have briefly looked at Access, found it distinctly inaccessible and have decided I don't have enough time to find out it works. Besides, the detention list i get at school comes in Excel and can be pasted straight into my spreadsheet.
Showed it to a colleague at work today and it met with the thumbs up!
Just have to use it for a few weeks and sort out any snags.
Cheers
Stewart