Sorting data to a cell by cell color.

[lukta]

I've tried Google and Youtube to look for the answer to my question but have so far come up short. I am pretty unfamiliar with excel so I think part of the problem is not knowing what to call my question.

So here it is. I have been working on a grade book spreadsheet that totals grades from 6 different categories: tests, midterm/final, class participation, attendance, group work, and homework. Each category has a different color in my total section of the spreadsheet. As it stands, when you add in new data, like a homework assignment you have to manually go to the total cell and add in the cell coordinates manually. I was wondering if there was a way to make my homework total cell totals look something like =SUM(all red cells) so I could just change the color of the cells and the totals would go to the correct spot. Now that I think of it, I only differentiate the color of my row 1 cells, the titles of the assignment, such as: Quiz 1. This would be in dark blue, but all the number cells are light blue so my grade sheet doesn't look like a bad 70's party.

I don't have a problem with manually adding the cells, but I want to be able to give this grade book template to some less tech savvy friends that might just find it easier to change the color.



I have another question as well. When I add a new assignment, besides having the new cell maximum go to the total homework maximum, is there a way I can automatically have the new data input in cells 2-20 be reflected in each students new total without having to manually add in Q2 to the new total and then dragging the box down the row to get Q3-Q20?

If either of these questions are unclear just let me know.

Thanks.

Edit: Using Excel 2011 for mac. Version 14.1.3

 

[lukta]

bump

 

[alansidman]

http://www.datapigtechnologies.com/flashfiles/SortonColor.html

not sure if this will work in a Mac environment

 

[lukta]

I think the color indexing is getting me on the right track. But what I want to happen is a bit different. I'm looking for a function that adds the scores for a row under different column headings. So, like in a gradebook, if I title the column HW-10/32 it will automatically turn orange. I want this to somehow signal that all grades in this column should then be totaled and then placed in my HW total column without manually having to type it in and drag the box down.

 

[lukta]

Bump.

Also, does anyone else have any grade book tricks up their sleeves? Or would they like to see what I have so far?

edit: Also, each column heading contains a short code for what the column will contain hw for homework, qz for quiz. Maybe there is a way to make a formula that would add up all the totals of columns with an hw into the homework total column.