Teachers mark book

Hi

Newbie here.

I want a mark book that has name, target grade actual grade

Teacher inputs target grade at start of year then progressively enters grades as work is done.. I want cells to colour as follows depending upon grade entered.

eg Target =A, first assessmsnt = B which is 1 grade below so cell goes orange. If C or less is entered cell goes red. If A is entered the cell goes green.

What about A*? How could that be accommodated?

Any ideas?

Thanks!!

Yo,

thanks! Your help is brilliant, but how would it cope with the A* ("A Star" grade) This can be input as A*, * or S

Thanks again

Hi

Thanks for this development, but I dont see it working.

What if the target grade changes? All the formatting needs to change also, and they dont on the sample supplied.

Thanks so much so far, but any chance of further development?

Thanks.

Crakkers

Hello.

thank you so much for your prompt replies so far.

I agree to your last post, however, what you have kindly developed so far does not really fit the bill, almost, but not quite! Also Cathy and Tins have grade C against B, this should be orange not red?

Thanks again

C!

Hi

Someone suggested that this can only be done via lookup table as part of the formula for the conditional formatting.

Thanks

Crakkers

Hello Crakkers
Have look at the attached file does this get close to what you need. If the Grade is equal or higher = Green, greater by 1 = Amber, greater than 1 = Red, using a lookup table and the shown formula.

DBY

Hello,

This is looking really good now! THANK YOU!

I shall give this a good look over and get back to you, but intial trials look fab!

Thank you

Crakkers

Hi

Thanks so far,

When I copy cells down the sheet works with formatting etc, but when I copy horizontally to add more rows of marks or new grade colums it loses the colour formatting. Does that make sense?

Thanks so far

Crakkers

Sample attached

Colour formatting does not copy when i inser multiple extra grades columns

Cheers

Originally Posted by gmr4evr1

Because the conditional formatting only applies to column C cells 3 through 25.

Thanks, I see that. maybe you could detail a solution?

Thanks
C!

Originally Posted by gmr4evr1

it would be helpful to know how many columns and/or rows you want to use.

Hi

So, its not a click a drag solved problem?

What if I said 30 columns of grades, then if i really only needed 25 I could simply delete them?

Thanks

Crakkers

Hi
You say you have 30 or so Grade columns are there associated Target columns adjacent to them? If so the formula can be amended to drag across. If you could clarify your layout it would help.

DBY

Hi
I know I gave the OP the solution using CF formulas. I have copied it across 30 or so columns with an amendment to the formula. I just need to check the OP's data layout.

DBY

No problem. I'd like to know if the Target columns are next to the grades, because the amended formula applies itself to every other column, so it then looks up the relevant Target adjacent to the Grade.

Hello Folks

Lots of talk here.

This is what I currently have

CF for Grades2 (1).xlsx

What I want is to extend the grades column across, say 30 times, and for the colour coding to remain.

Cheers

Crakkers

Are the Targets adjacent to each grade column? For example: Target Grade; Target Grade... and so on.

Hi

No, one target column, 25 marks / grades columns.

Crakkers

Hi
My solution similar to newdoverman except I was using:

Formula:

Please Login or Register  to view this content.

To drag across you change the absolute value of column C to relative:

Formula:

Please Login or Register  to view this content.

Apply the criteria as previously.

DBY

Hi

I have changed your spreadsheet that you have kindly tried to fix the formatting error. This is uploaded. What I want is just 1 target column and many grade columns. the grade column is always compared to the target column. But the target may change through the year and as a result the formatting should change. I regret to say this latest version still does not appear to work.

Thanks for your attention to date

C!CF Grades Example 3.xlsx

Hello
I did give you the solution to this in my last post at the end of page 2. I've amended the above file. Is this OK?

DBY

Hello

That appears to have nailed it!

Thank you!!!!!!!!!!!!

Guess what................. we have a google docs system at school and we have to upload it into google sheets....................... It loses the formatting in google sheets!!!!!!!!

Ahhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh!

Ahhh! now you tell me! I'll have a play around with Google Docs to see if I can get something similar to work.

Hi
Not used Google Sheets before and it appears that the CF formulas can be applied. But it doesn't seem to recognize Name ranges in the CF custom formula, so you'll have to reference the cell ranges of the Grades. Otherwise it seems to work as Excel.

DBY

Thanks everyone, this has been really useful for me too.

One thing I can't figure - in the MATCH function you list ($C3,Grade,0), where Grade obviously refers to the L Column and you've defined A*-D. How do I change that? Simply I want to add on E,F,G and U as it's that kind of a school! I get a restricted value message

Got it, sorry - Data tab, Data Validation.