Posts by jacjas

    Hi


    I have a work book with two worksheets.


    the master and a summary table


    I need a formula that can look at an id number in my summary table and locate that id number in the master table it will appear multiple times and then I need it to look at another column in my master data and count if for that id number the other column has a specific text string so for example.


    for id number A09669 there are a count of 3 entries for the action a written comment and for the same ID a count of 1 entry for the Action Kept Behind


    please see attached

    i have two columns of data and i need to highlight or identify in another column using a word (e.g match), those lists where the cells side by side both contain a 5 or a 5 and a 6, the cells must both have a 5 in them or a 5 in one and a 6 in another to be a match, i have 1580 rows and dont want to have to do this manually, can anyone help with a formula or something so i can easily pull out the rows of data where this match criteria is met.
    [TABLE="width: 128"]

    [tr]


    [TD="class: xl63, width: 64"]


    [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"][/TD]
    [TD="class: xl63"][/TD]

    [/tr]


    [tr]


    [TD="class: xl63"][/TD]
    [TD="class: xl63"][/TD]

    [/tr]


    [tr]


    [TD="class: xl64"][/TD]
    [TD="class: xl64"][/TD]

    [/tr]


    [tr]


    [TD="class: xl63"][/TD]
    [TD="class: xl63"][/TD]

    [/tr]


    [tr]


    [TD="class: xl63"][/TD]
    [TD="class: xl63"][/TD]

    [/tr]


    [tr]


    [TD="class: xl64"][/TD]
    [TD="class: xl64"][/TD]

    [/tr]


    [tr]


    [TD="class: xl64"][/TD]
    [TD="class: xl64"][/TD]

    [/tr]


    [tr]


    [TD="class: xl64"][/TD]
    [TD="class: xl64"][/TD]

    [/tr]


    [tr]


    [TD="class: xl63"][/TD]
    [TD="class: xl63"][/TD]

    [/tr]


    [tr]


    [TD="class: xl63"][/TD]
    [TD="class: xl63"][/TD]

    [/tr]


    [tr]


    [TD="class: xl63"][/TD]
    [TD="class: xl63"][/TD]

    [/tr]


    [tr]


    [TD="class: xl64"][/TD]
    [TD="class: xl64"][/TD]

    [/tr]


    [tr]


    [TD="class: xl63"][/TD]
    [TD="class: xl63"][/TD]

    [/tr]


    [tr]


    [TD="class: xl64"][/TD]
    [TD="class: xl64"][/TD]

    [/tr]


    [tr]


    [TD="class: xl63"][/TD]
    [TD="class: xl63"][/TD]

    [/tr]


    [tr]


    [TD="class: xl64"][/TD]
    [TD="class: xl64"][/TD]

    [/tr]


    [tr]


    [TD="class: xl64"][/TD]
    [TD="class: xl64"][/TD]

    [/tr]


    [tr]


    [TD="class: xl63"][/TD]
    [TD="class: xl63"][/TD]

    [/tr]


    [tr]


    [TD="class: xl63"][/TD]
    [TD="class: xl63"][/TD]

    [/tr]


    [/TABLE]

    Re: calculate percentages based on multiple columns of data


    Hi


    thank you for that but no I don't think it is doing what I needed.


    I need to see the percentage of students from each subject making below expected progress, then the percentage making expected progress then the percentage making expected and above progress at each grade week.


    I think this needs to calculated using the column "levels below/above GW1", "levels below/above GW2","levels below/above GW3","levels below/above GW4", so the formula will look at, identify and count the number of students within each subject, then looking at the above mentioned columns individually calculate the percentage of students within that subject that are below, so anything with a - the percentage that are meeting, so anything that is at 0 and above, then the percentage that are exceeding, so anything that is above 0.


    The idea is once this data has been collated we can produce some graphs to show the progress achieved or not as the case may be, at each grade week over the academic year. I hope that makes sense


    The table of data would look like below but complete:
    [TABLE="width: 1501"]

    [tr]


    [td]

    Subject

    [/td]


    [td]

    % below GW1

    [/td]


    [td]

    % meeting GW1

    [/td]


    [td]

    % meeting and above GW1

    [/td]


    [td]

    % below GW2

    [/td]


    [td]

    % meeting GW2

    [/td]


    [td]

    % meeting and above GW2

    [/td]


    [td]

    % below GW3

    [/td]


    [td]

    % meeting GW3

    [/td]


    [td]

    % meeting and above GW3

    [/td]


    [td]

    % below GW4

    [/td]


    [td]

    % meeting GW4

    [/td]


    [td]

    % meeting and above GW4

    [/td]


    [/tr]


    [tr]


    [td]

    Art

    [/td]


    [TD="align: right"]28.6%[/TD]
    [TD="align: right"]57.1%[/TD]
    [TD="align: right"]6.0%[/TD]
    [TD="align: right"]26.2[/TD]
    [TD="align: right"]71.4[/TD]
    [TD="align: right"]8.3[/TD]
    [TD="align: right"]14.3[/TD]
    [TD="align: right"]83.3[/TD]
    [TD="align: right"]10.7[/TD]
    [TD="align: right"]15.5[/TD]
    [TD="align: right"]83.3[/TD]
    [TD="align: right"]14.3[/TD]

    [/tr]


    [tr]


    [td]

    Design and Technology

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Drama

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    English

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Humanities

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Information Technology

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Mathematics

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Music

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Physical Education

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Science

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Spanish

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Stem

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    French

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Key Skills

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    Hi


    I have a set of data based on individual student assessments.


    The data shows the individuals grades at each assessment point and the number of levels they are below, on or above target by for each subject.


    I want to use this data to obtain some Subject based graphs.


    I need a formula, ( I have tried pivot tables and failed !!!) that will look at the data and for example for "art" tell me the percentage of students that are making expected progress (or better), students making greater than expected progress and the students below expected progress for each assessment point (grade week GW1, GW", GW3 and GW4)


    When I use a pivot table I can get it to work for 1 grade week but when I add the additional weeks data as a separate field it simply repeats the data from Grade week 1. I can create a pivot table for each week and that works fine changing the value field to a percentage calculation but then I cant join them up and grouping them is difficult as I need the students meeting and exceeding expected progress in one group and a separate group for students making expected progress.


    any help greatly appreciated ...pulling my hair out now

    Re: Multiple charts from a pivot table


    Cracked it the below VBA code worked perfectly from http://www.contextures.com/xlPivot09.html


    Sub PrintPivotCharts()
    'pivot table tutorial by contextures.com
    'prints a chart for each item in the page field
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Set pt = ActiveChart.PivotLayout.PivotTable
    For Each pf In pt.PageFields
    For Each pi In pf.PivotItems
    pt.PivotFields(pf.Name).CurrentPage = pi.Name
    ' ActiveSheet.PrintOut
    ActiveSheet.PrintPreview 'print preview for testing
    Next
    Next pf

    Re: Multiple charts from a pivot table


    Hi


    many thanks for your reply this is greatly appreciated, I have tried the "show report Filter pages" option and whilst this is a great function it didn't take the pivot chart onto the other pages and that is essentially what I need it to do. I shall try to search for some VBA as you suggested to record a Macro but have no experience is this area, but like to try :) is there any other software packages I could use that you can think of to achieve this ?


    thanks again


    Jackie

    Can anyone offer and help with this issue.


    I have a pivot table and a pivot chart.


    the chart is based on one student filtered down to within my pivot table.


    I want to print the chart for each student ( 230+)


    Can this be done automatically in some way as currently my only option is to do it one by one by changing the student using the filter within the Pivot table, copying the refreshed pivot chart out to pdf and Print which is unmanageable due to time restraints


    or should I be using another way entirely ?

    Re: formula for an IF and CONTAINS


    No problem, its a list of groups in the following format i need the cell im in to look at this and return the first number yes to the left of a character would work.
    7N1
    7N2
    7C1
    7C2
    7W1
    7W2
    7T1
    7T2
    7R1
    7R2
    7B1
    7B2


    8N1
    8N2
    8C1
    8C2
    8W1
    8W2
    8T1
    8T2
    8R1
    8R2
    8B1
    8B2


    9N1
    9N2
    9C1
    9C2
    9W1
    9W2
    9T1
    9T2
    9R1
    9R2
    9B1
    9B2


    10N1
    10N2
    10C1
    10C2
    10W1
    10W2
    10T1
    10T2
    10R1
    10R2
    10B1
    10B2


    11N1
    11N2
    11C1
    11C2
    11W1
    11W2
    11T1
    11T2
    11R1
    11R2
    11B1
    11B2

    Re: formula for an IF and CONTAINS


    UPDATE:



    I have tried a nested IF =IF(F2="7N1",7,IF(F2="7W2",7,IF(F2="7R2",7,0))) this works but will need a nested IF with 60 different variations, I have tried to use a wild card =IF(F2="*7*",7,0) (its looking at F2 which has 7N1 in it yet returns a zero so the wildcard isnt working. i was going to use this if it worked in a nested IF.


    so i have 60 groups all starting with either a 7, 8, 9, 10 or 11 with a letter and number behing it that varies, i just want to populate another cell with the first number

    I am trying to get a cell to look at another cell and if the cell contains a number return that value in the cell i am in so for example if cell "F1" contains "7B1" return 7 in Cell A1.


    My list in column "F" will vary numerically from 7 to 10 and the text will vary also

    Re: change a worksheet tab colour dependant on the contents on a range of cells


    That's great thank you for all your time and hard work on this,I'm a complete novice with vba so just to check this is what I did.
    I added the code by right clicking on the first worksheet tab, selected insert code then located the first sheet in the workbook and right click insert module added the code and changed the range, ( on this tab, H1:H11) it worked but it ran the macro for the whole workbook, ideally I want to add a code for each worksheet as the range changes on each sheet, is that possible ? once again many thanks

    Hi


    I have a worksheey of data and i would like the tab to be a certain colour dependant on the the contents of a range of cells within that worksheet is that possible. i know i need to use VBA and it cna be done based ont he contents of one cell.


    So for example i have a sheet of people i am awaiting payment from, based on the last column " bill paid" i want the tab to be Red if there are any blanks in this column/ range and go green when all the cells in the column/ range are filled with "y"


    can anyone help or this not possible ?


    many thanks in advance


    jackie