The_Original_Invisible

You can do it with an array formula such as {=INDEX($C$2:$C$11,MATCH($E3,IF($B$2:$B$11=F$2,$A$2:$A$11),0))} This assumes you've layed your results table out thus:- Blank Maths English Science ICT History Fred Sally This assumes that $E3 = Name of student and F$2 = Subject Remember to Ctrl Shift Enter it to get the curly bracket, don't try and type them Working example available but you'll need to pm me. I'm in a band and off gigging tonight but can look tomorrow for you Regards Lee

5 years or so, yeah. But I'm self taught and have just picked things up as I've gone along I tend to use specific Excel forums (or used to use when I was less experienced) for Excel queries. Now I make sure I keep all my formulas in one place to refer to at a later date. No point re-inventing the wheel! A good newish one is the Excel User Group The people on it are local and very friendly and helpful. I attended a conference of theirs last month and they're putting another one on later this year I believe? Lee

Have a look at this code Sub ConsolLoop() Sheets(4).Select Cells.ClearContents r = 0 n = 0 For i = 1 To 3 Sheets(i).Select GoSub DoCopy GoSub DoPaste n = n + r Next i Exit Sub DoCopy: Cells(1, 1).CurrentRegion.Select Selection.Copy r = Selection.Rows.Count Return DoPaste: Sheets(4).Select Cells(1, 1).Offset(n, 0).Select ActiveSheet.Paste Return End Sub It merges all data from the first 3 worksheets in to the 4th worksheet I'm sure you'll work out how to adapt it to meet your requirements Hope it helps Lee

I expect you've worked it our from my previous mail, but... =SUMPRODUCT(--($B$1:$B$5={"J","K"})*($A$1:$A$5=1)) Lee

In this instance it doesn't make any difference, but in simple terms it counts the number of rows where the criteria is met. If you wanted to sum values you would leave the -- out. You could put in *1 instead of -- if you wanted to. The double unary is the neatest to me though! :-) If your data was layed out thus:- 0 J 10 1 K 20 1 J 30 0 L 50 1 L 60 And you wanted to know the sum of column C then you would use =SUMPRODUCT(($C$1:$C$5)*($B$1:$B$5="J")*($A$1:$A$5=1)) Which says give me the sum of $C$1:$C$5 when $B$1:$B$5="J" and $A$1:$A$5=1 ( and you'd get the value 30 returned) By the way, if you wanted to be REALLY clever, you could also put in multiple criteria such as =SUMPRODUCT(($C$1:$C$5)*($B$1:$B$5={"J","K"})*($A$1:$A$5=1)) Which says give me the sum of $C$1:$C$5 when $B$1:$B$5="J" or $B$1:$B$5="K" and $A$1:$A$5=1 ( and you'd get the value 50 returned) Hope that makes sense? If not, there's another more detailed explanation of it here McGimpsey & Associates : Excel : Formulae : Why "--" Lee

=SUMPRODUCT(--($B$1:$B$5="J")*($A$1:$A$5=1)) Hope it helps Lee