"SandyA" wrote:
.. I don't think that will work
because some of the data is text rather than numeric.

Some thoughts ..

CF can support up to 3 triggers (4, inclusive of the "default" no-trigger
fill). Provided it's within this CF limit, text is not a problem if the
cells contain standard text strings to either match or to be searched (if the
lookup standard text is housed within text strings).

We could effect the same trigger criteria for standard text on the summary
page. For eg, let's say we have 3 criteria to color trigger in the summary
page's col A (say), presumed consistent with the criteria applied in the
other 100 sheets' col A:

Text1 - yellow fill
Text2 - green fill
Any numbers exceeding 500 - red fill / white font, bolded

(Col A presumed to contain either text, eg: Text1, Text30, etc
or real numbers: 500, 100, etc)

Try in a new sheet, say Sheet1 ..

Select col A (A1 active)
Click Format Conditional Formatting
Make the settings under conditions 1 to 3 (sequence is important) as :
Cond1: =TRIM(A1)="Text1"
Cond2: =TRIM(A1)="Text2"
Cond3: =AND(ISNUMBER(A1),A1500)
Trigger formats applied for Conds 1 to 3 a
yellow fill, green fill, red fill / white font, bolded

Test it out .. Input anywhere within col A, and we'd get:

501 (red fill, whote font, bolded)
Text1 (yellow fill)
499 (no trigger)
Text2 (green fill)
1000 (red fill, whote font, bolded)
Text1 Text2 Text3 (no trigger)
Text2 Text1 Text3 (no trigger)

In another new sheet (Sheet2, say),

Apply the CF to col A as per earlier
except now we use as the cond format formulas:
Cond1: =ISNUMBER(SEARCH("Text1",A1))
Cond2: =ISNUMBER(SEARCH("Text2",A1))
Cond3: =AND(ISNUMBER(A1),A1500)
(Apply same trigger formats for Conds 1 to 3 as earlier)

Test it out with the same inputs within col A, and we'd get:

501 (red fill, whote font, bolded)
Text1 (yellow fill)
499 (no trigger)
Text2 (green fill)
1000 (red fill, whote font, bolded)
Text1 Text2 Text3 (yellow trigger)
Text2 Text1 Text3 (yellow trigger)

Text1 Text2 Text3
will now give yellow trigger as Cond1's formula will evaluate to TRUE,
unlike in Sheet1

Text2 Text1 Text3
also gives yellow trigger as well (despite Text2's apparent position in the
string ahead of Text1) as Cond1's formula will evaluate to TRUE ahead of
Cond2 (the CF evaluation sequence is similar to an IF eval sequence [from
left-to-right] evaluating from Cond1 Cond2 Cond3)

If we had reversed Conds 1 and 2 in Sheet2 (with the same trigger color
fills applied: Text1 - yellow, Text2 - green), ie used instead:
Cond1: =ISNUMBER(SEARCH("Text2",A1))
Cond2: =ISNUMBER(SEARCH("Text1",A1))
Cond3: =AND(ISNUMBER(A1),A1500)

then we'd get:
Text1 Text2 Text3 (green trigger)
Text2 Text1 Text3 (green trigger)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---