Posts by jbanks

    Thanks for this, i had to tweak the order of the formula to make it work as the match was =INDEX(Log!E:E,MATCH(B530&" "&A530,Log!A:A,0)) - my fault! but weirdly its only picked up 10 of the 50 students who I have attendance data for.

    Any ideas why when I have filled down it hasn't picked up all 50?



    I have attached a testfile which will hopefully assist anyone kind enough to help.

    Basically, in the student tracker sheet I want to be able to pull the attendance and late data of the student in each row from the Log sheet into the blue and yellow cells.

    I need it to match the names in the student tracker file with the log file and then pull the attendance and late figures.

    I have tried {=index(Log!E:E,MATCH(StudentTracker!A6&StudentTracker!B6&StudentTracker!C6,Log!A7:A1636&Log!B7:B1636&Log!C7:C1636,0))} where the cell references are from my larger 'real' file but get no luck

    Totally appreciate my attempt may be completely wrong!

    Any help would be great.




    I want to be able to scan row 2 to count/find all the 7OE cell entries (A2 and I2) and count them if there is a Y next to it. In the example below it would return 1 as the same 7OE text found in I2 has a k in J2 and would not be counted. I want to be able to use a wildcard as I would extend this for other subjects, e.g., looking at C2 I would want to use *ES1* in the formula as I will be using a separate reference list which does not match the codes below which include staff, room and subject.

    I have tried =COUNTIFS($J$5:$GG$5,7OE*, $J$5:$GG$5,"y") and various variations of it where J5:GG5 is the range I want to scan in my larger file but with no luck, either getting a #VALUE error or 0.

    Any ideas? Thank you for taking a look at it and any help provided. I have attached the table below to this thread as an Excel file.


    A B C D E F G H I J
    1 ThuA:1 Present? Y or N ThuA:2A Present? Y or N ThuA:3A Present? Y or N ThuA:4A Present? Y or N FriA:1 Present? Y or N
    2 70E KM F6 - English y 7n/Es1 TD F6 - English y 7N1/En FXO F6 - English y 7G/Gg1 JXG F6 - English   70E KM F6 - English k
    3 70I LJR F4 - English   7o/Pe2 AN N 7O1/Ma IC F4 - English N 7o/Es1 TD F4 - English n 70I LJR F4 - English k

    Hi All

    Thank you for reading.

    I have attached a testfile to try and explain what I am trying to do and am really not sure how to do it.

    I work in a school and have a record of all the students and their negative behaviour issues by term (Aut1, Aut2, Spr1 and Total). Some students are classed as LAC, PP etc and I am trying to come up with a way of Adding up all the incidents that students in a particular year group, who are in a particular classification have got,.

    So in the table below which I have attached (made up names- testfile.xlsx) I would like a formula that would pull the LAC students from Year 8 for the Aut1 column and return the value 19, I can hopefully edit it to suit my needs after that.

    Massively appreciated any assistance on this



    Here is the file: testfile.xlsx

    Sname Fname M/F Year Total Incidents Spr1 Aut2 Aut1 LAC PP
    Adam Adams M Year 8 21 0 5 16 LAC  
    Billy Villison M Year 7 1 0 0 1    
    Colin Colinson M Year 9 13 0 4 9   PP
    David Davies M Year 8 3 0 0 3 LAC  



    In the attached I have hundreds of students (anonymised), they are all in different years groups. To the right I want to be able to pull the top 10 students from each of the year groups based on the points total.

    I know I can use filters but I want to use formulas which will update the lists when the points values change in the future.

    Please help!



    I HAVE SOLVED THIS PROBLEM. Can't find how to delete it.


    Thank you for taking the time to read this.

    I have created a VBA Userform which collects various pieces of information one of which is a Quantity field where a whole number is entered. When submitted the data is stored in an Excel Sheet called Orders and it does what I want it to do except one issue.

    The issue is that the quantity field from the VBA form, e.g., 1, 2, etc is stored isn't recognised in the spreadsheet as a number and this is an issue because I have a profit sheet which copies the value from Quantity field and is used in a formula on the profit sheet but as its not being recognised as a number the formula doesnt work.

    Therefore could anyone tell me how I can either get the Quantity number that is entered to be recognised as a number please. I dont know if its the userform that needs amending (I have very limited VBA knowledge) or whether there is something in Excel which will do this automatically for me.

    Here is the code I have used from my VBA form which takes the data over when the submit button is clicked:

    and here is the code I have used on my Quantity box which stop text being entered

    Private Sub QuantityTextBox_Change()
        If Not IsNumeric(QuantityTextBox.Text) Then
            QuantityTextBox.Text = ""
        End If
    End Sub

    Any help would be very much appreciated.



    Re: Help. Drop Down Menu to return table of data

    Thank you so much Batman, thats exactly what I want.

    Is there a tutorial or youtube video I can watch that can show me how to set it up?

    As I would like to learn how to set it up for myself.

    Thanks again


    Help. Drop Down Menu to return table of data


    I'm really stuck with this and not sure where to start and cant find a tutorial anywhere which may be because I am searching for the wrong keywords.

    Basically I work in a school and want to do the following:

    On Sheet 1 have a drop down menu of all the subjects we offer, in sheet 2 I have a table of data for each subject which looks like this (only bigger)

    C B A
    3 34 45 52
    4 23 63 34
    5 12 23 53

    The C B A refer to achieved grades at the end of a course and the 3 4 5 refers to the level they started at when they joined the school. The numbers, e.g., 34 refer to the number of students who for example joined the school on a level 3 and went on to achieve a C grade when they left.

    Basically what i want to do is when I select the subject from the drop down on Sheet 1 it will pull the relevant table of data from sheet 2 AND display it in the space on Sheet 1.

    Can anyone please help! Totally stuck!

    Thanks so much


    Re: Counting Cells with Conditional Formatting

    Thanks for your advice, I have now attached an exert.

    I am not sure if I can use the same Conditions ot do a COUNTIF as a child may have a Target of a B in English and A in Maths, however their current progress grades could be both Bs therefore one would be shaded WHITE (equal to Target) and the other RED (below Target), unless I have misunderstood how I could use COUNTIF in this example.

    I hope the attached may help! Also in Work I use Office 2007 so any solutions using that would help too!

    Thanks again!



    I have read various threads regarding this but due to a lack of VBA knowledge I get lost in them! Therefore, I apologise if you feel this has been answered previously but I'm hoping by explaining my situation, it may make more sense to me -

    So here goes!

    I work as a teacher and my role is the analysis of data, something I can do fairly easily using my Excel knowledge but I want to take it a step further (maybe this isn't the best was and another suggestion would be great.

    The spreadsheets I use have 400 students in columns A (first name) and B (last name), in columns C to AC I have the students targets for the 26 subjects/courses we offer, a student would only study between 10 and 14 of these so in a row there would be blanks. In cells AE to BE I have the students current grades (those which show current situation/progress). The first student would be in ROW 2.

    I want to show whether a student is below, equal to or exceeding their target and have done this using Conditional Formatting (3 separate conditions) using RED for below, White for Equal to and Green for Exceeding.

    I now want to count how many of each colour there are in each row to quickly work out how many of the subjects the students are falling behind in so we can focus our efforts on these.

    Any help would be great (please keep it straightforward as possible!) and if you can link it to my cell references that'd be even better!

    I can supply an exert if this would make it easier!

    Thanks ever so much for any suggestions (of this isn't the best way!) or any specific help!

    A. Teacher!