Grading Report Issues

[Rusty8750]

Hello All

I hope you can asssit me... I am creating a report to grade rooms and I would like to see if it is possible to do the following:

I would like the report to automatically color the room number based on the letter grade... for instance... Cell A4 is "101" and Cell B4 is "A" I would like cell a to automatically color green. But at the same time set the formula up to cover Grade B - Yellow and Grace C - Red.

Also, I would like to see if there is a way to calucalte the number of A's, B's and C's on the spreadsheet.

[Moo the Dog]

Try this:

Select B4:B51, and create three new Conditional Formatting rules, and set up the appropriate fill colors:
Rule #1 (Green): =C4="A"
Rule #2 (Yellow): =C4="B"
Rule #3 (Red): =C4="C"

Click OK once or twice to apply. Then, copy B4:B51, select D4:D51 and click 'Paste Special: Merge Conditional Formatting'. Then select F4:F51 and again, select 'Paste Special: Merge Conditional Formatting'.

For the counts, enter this formula in cell L4: =COUNTIF($B$4:$G$51,I4)

Then copy and paste it into L5 and L6.

Should be good to go.

- Moo

[Rusty8750]

Thank you soooo much that seems to work... When the cell in blank the room number is green, is there a way to keep it blank or non colored?

[Moo the Dog]

That's odd, I don't get that at all.. see my attached sheet (with modifications in place).

- Moo

[Rusty8750]

Thanks Moo, I mustve done something incorrect!!! I appreciate your help!

[Rusty8750]

I am working on the same spreadsheet as above,however, I am trying add a new element. When the room is graded in Column C6 with a B or a C, I would like the room number to populate in C48 and below in a new chart where we can list the reason why the room was graded as is. I appreciate any help you can be.

[Moo the Dog]

Not sure if this is what you want, but it does return the room numbers for each column that are assigned a B or a C (up to 10 rooms per column each). See the attached sheet. Started range in row 53, since row 48 was still part of your original layout.

- Moo

[Rusty8750]

So, yes that is exactly what I want to do, however, I tried transferring your formula to the revised spreadsheet and it doesnt seem to want to work... what am I doing wrong??? I really appreciaite your help!

[Moo the Dog]

I fixed the file/formulas for you. The formula is an array formula, meaning it has to be applied using Ctrl + Shift + Enter, instead of just Enter. And you don't type in the curly brackets { }, Excel will add them for you when you when you type the formula in the formula bar (or in the cell) and hit Ctrl + Shift + Enter.

I think your example sheet started in row 4, so the formula had to be modified to account for the 7 rows above the starting row (row 8) in your new example. So for the first formula, in B55, it is:

Please Login or Register  to view this content.

Again, applied with Ctrl + Shift + Enter, then filled down.

See the modified sheet attached. I hope it solves the issue for you. =)

- Moo

[Rusty8750]

Wow, Moo you are amazing!!! One more quick question, is it possible when the numbers populate on the bottom spreadsheet that the information stays with that room number... ie, if room 311 has an explanation then I add room 310 as a B it bumps 311 down a row leaving the explanation for room 310 - does that make sense!?

[Moo the Dog]

Glad to help!

As to your new question, without using VBA code, I don't think there is a way to do what you are asking. I'm not sure how to attack it in VBA myself, but I can try bringing in some of the Guru's around here and see if they might be able to assist.

* A basic way would be the old copy/paste method to move the items down, then fill in new data for the recently changed room#.
- Moo

[Rusty8750]

Thank you for your help I truly do appreciate it... Cut and paste I think is the best solution I will definately look for you again