# Posts by jacjas

• ## look up and count if ???????

Re: look up and count if ???????

hi

apologies for the delay in responding we have been on holiday, this doesn't work I'm afraid

• ## look up and count if ???????

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

• ## remove a zero result from a Vlookup concatenate and if(iserror formula

Re: remove a zero result from a Vlookup concatenate and if(iserror formula

Whoo Hooo !!! once again solved like a flash thank you so very much :thumbcoo:

• ## remove a zero result from a Vlookup concatenate and if(iserror formula

Hi

I have the below formula:
=IF(ISERROR(VLOOKUP(CONCATENATE(\$A12," ",AH\$1),yr10gw6,22,FALSE)),"",(VLOOKUP(CONCATENATE(\$A12," ",AH\$1),yr10gw6,22,FALSE)))

This is working great and has removed all my #N/A results but i am getting a 0 result showing what do i need to add to this to " hide / not show" the 0 results ?

any help greatly appreciated.

• ## look at two lists and identify where two cells side by side contain a 5

Re: look at two lists and identify where two cells side by side contain a 5

once again a perfect solution thank you very much :hyper2:

• ## look at two lists and identify where two cells side by side contain a 5

Re: look at two lists and identify where two cells side by side contain a 5

sorry hope that has worked this time

• ## look at two lists and identify where two cells side by side contain a 5

Re: look at two lists and identify where two cells side by side contain a 5

 4a 4b no match 5c 4b no match match 4a 4a no match 3b 4b no match match match match 4b 4b no match
• ## look at two lists and identify where two cells side by side contain a 5

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]

• ## calculate percentages based on multiple columns of data

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]

• ## calculate percentages based on multiple columns of data

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

• ## Multiple charts from a pivot table

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

• ## Multiple charts from a pivot table

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

• ## Multiple charts from a pivot table

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 ?

• ## formula for an IF and CONTAINS

Re: formula for an IF and CONTAINS

that is brilliant and works perfectly, much easier than a nested if with 60 variations !!!! thank you so much for your help very much appreciated.

• ## formula for an IF and CONTAINS

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

• ## formula for an IF and CONTAINS

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

• ## formula for an IF and CONTAINS

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

• ## change a worksheet tab colour dependant on the contents on a range of cells

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

• ## change a worksheet tab colour dependant on the contents on a range of cells

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

Briliant thank you i will wait for the last part and then try this i didnt even know if it was possible very happy thank you.

• ## change a worksheet tab colour dependant on the contents on a range of cells

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 ?