Anyone a conditional formatting guru?

[pickytweety]

Hi,
Below I have an example of a spreadsheet that summarizes testing scores.
The Kindergarten teacher liked it so much that now she wants a file for each
grade! The way it works is that a green cell means the student is not at
risk. A pink cell means the student is at risk and a yellow cell is
borderline. I haven't done much with Conditional Formatting, but it was
pretty tedious highlighting each range and putting in the criteria, so to now
do it for each grade seems like a long project. I figure someone out there
knows a better way. Is there any way to set up a table that lists criteria
and ranges for conditional formatting? How would an expert approach this
task? I'll include pictures below so it's easier to understand. The arrays
tabulate the scores. Rows 4,5, 9, 10 and 11 are informational and are not
referenced in the formulas. The tests given change between fall, mid-year,
and spring as do the categories for risk level.
--
Thanks,
PTweety

Ok it's not letting me put the picture in here. Too big? If you're a
Conditional Formatting guru who would like to help me, send me your email
address and I'll send you the file.



FALL

INITIAL SOUND FLUENCY LETTER NAMING FLUENCY WORD USE FLUENCY

<4 Deficit <3 At Risk <7 At Risk
4-7.9 Emerging 3-7.9 Some Risk 7-17.9 Some Risk

=8 Established >=8 Low Risk >=18 Low Risk

MA-student 1 14.8 24 0 38.8
BA-student 2 2.6 8 7 17.6
SC-Student 3 5.1 38 7 50.1
AC-Student 4 0 10 6 16.0
KC-Student 5 4.7 12 1 17.7

The conditional formatting starts with the cell containing 14.8. I'm using
Excel 2000 but the teachers have Office 2007 with the ribbon menus.

 

[Bernie Deitrick]

PTweety,

You can (almost) do anything with CF that you can do with worksheet formulas - all you need to do is
figure out a formula that works, that returns TRUE or FALSE, and that updates correctly as the cell
it is applied to changes.

For example, if your lower limit is given in a table - say, in cells A15

Grade Fall Winter Spring
1 2 2.2 2.4
2 4 5 6
3 7 8 10
4 11 12 14

etc.

Then you could compare the scores to that table - say that this is in Cells A78

Name Grade Session Score
Fred 3 Fall 6

by using a formula like this for the score in cell D8, where your table of limits is in Cells A15.

=D8<=VLOOKUP($B8,$A$2:$D$5,MATCH(C8,$1:$1,FALSE),FALSE)

Then that could be appplied to cell F8 if you extended to this in cells A7:F8

Name Grade Session Score Session Score
Fred 3 Fall 6 Winter 8.2

But without an actual working spreadsheet......

HTH,
Bernie
MS Excel MVP

 

[pickytweety]

Ok so after reading your post several times and dropping your example into
Excel, I'm sitting here scratching my head. Did you answer a question other
than the one I asked, or am I too stupid to understand it's application? My
email address is (e-mail address removed). Would you email me and I can reply with
the file attached? Unless there is some other way I can show you this
file....

I'm not understanding how the True/False relates--I was after one of three
colors. I'll have to do separate files for each grade because they have
different tests. It's not one set of data with all grades in it. The array
formulas looking up the scores are working, I was just hoping to refer to a
table for color criteria, but it's not letting me. The error I get is "You
may not use references to other worksheets or workbooks for conditional
formatting criteria."

 

[Shane Devenshire]

Hi,

Your table has nine or ten levels, but you indicate 3 colors of coding. You
don't need a reference range at all, but I do need to understand what scores
trigger the three colors. Lets suppose <7 is pink, 7-17.9 yellow all others
green.

The cute way is to format all the cells to Green first, just straight fill
color. Now since you didn't say if you were formatting the score cells in
one column of the entire row or each of the scores independently the answer
may or may not be correct.

Select all the cells you want formatted and choose Format, Conditional
Formatting, choose less than from the first drop down and enter 7 in the next
box. Pick the color pink. Click OK once and in the conditional formatting
box click Add and choose less than or equal to and enter 17.9 in the second
box...

 

[pickytweety]

Hi,
Each test, each grade, each season has it's own set of criteria for if a
child is at risk. I'm planning on doing a different file for each grade
level K-9, but was hoping to be able to just change the numbers rather than
actually going into Conditional Formatting a zillion times. I'm not changing
the color of the whole row, just the cell the score is in. To be more
detailed.... if a child's score is under 7 on their "Word Use Fluency" test
in the Fall, they are at risk and their score of 6 should have a pink
background. There are 5 or 6 different tests each season and they each have
criteria for "at risk", "some risk" or "low risk". Multiply that by 9 grades
and I would be going into Conditional Formatting an awful lot. I'm willing
to send a sanitized file to someone if they're willing to have a go at it.

 

[Don Guillett]

Is your system clock off or did you purposely falsify the time so your post
would be at the top. In either case, I routinely delete these messages.

 

[Roger Govier]

Hi

If you want to mail me a copy of the file, I'll take a look tomorrow. (it's
now 11:30 PM in the UK)
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address

 

[Bernie Deitrick]

For anyone following this, Candi Ray emailed me the file, and one reason that the CF didn't work was
that the table was on another sheet - creating dynamic range names fixed that, allowing the other
sheet to be used as the source of the data for the CF. I also converted the VLookup/match combo to
a SUMPRODUCT formula, to change the table to a database rather than a cross tab table.

HTH,
Bernie
MS Excel MVP

 

[pickytweety]

Were you talking to me? I didn't change my clock. The administrative powers
that be lock my computer down so tight that I don't even have access to
change my clock. Drives me crazy at the daylight savings change.