Referencing conditional format is another workbookHI

[Kay]

HI all,

I hope someone can help me come up with code that will conditionally format
a summary sheet(scorecard) based on the conditional formats in another
workbook. I used Excel driven conditional formatting in my main data sheet
which works great, but the problem is with the final scorecard.

The summary is really a scorecard and only needs color indicators. So when
I sell products in a country each product has allowable market targets.(high
and low). The conditional formatting is easy to do in the database
worksheet. The requirements of the scorecard are such that if any one
product is red in the database, the metric in the scorecard is flagged with
red , if there are no reds but there is a yellow, it flags yellow, etc.

Any ideas would be very appreciated.

 

[Kay]

Joel,

I understand that technique, but that does not fulfill this requirement.
The summary worksheet is a much higher level report where a cell would
conditionally format if any cell in the previous worksheet has that color.
As an example. If Product A is within a certain range it might turn red, but
Product B,C,D are either yellow or green. Red must show in the summary sheet.

I hope this makes the requirements clearer and your continued input is
appreciated.

 

[Kay]

Thanks Joel...I usually check Chip's site first, but didn't this time. I did
name the ranges so I could refer to the named range in the conditional format
formula, but I think the formula needs to be more robust.

 

[GS]

Kay formulated on Saturday :

Thanks Joel...I usually check Chip's site first, but didn't this time. I did
name the ranges so I could refer to the named range in the conditional format
formula, but I think the formula needs to be more robust.

$0.02...
Curious! -Is there some reason why the CF applied to the source data
sheets can't be used on the summary scorecard sheets? Seems odd that
whatever CF criteria was used to 'flag' cells on the source sheets
can't be used on the summary sheet, given they both contain the same
data.

regards,

 

[Kay]

Gary,

Great question, but the odd thing is that each product has a different range
of acceptable targets. Thus, the conditional format references cell
addresses rather than values. =AND(TRIM($J6)<>"",$J6<$M6). While the majority
of records could have a green status if one record has a red status, the
scorecard must show red. I was thinking maybe you could reference the color
index number. Something like...colorindex. In fact according to Chip
Pearson's site, you can with his VB Module. I have imported that and
successfully tested on cells that I apply direct fill color to. However, I
can't get this to work with the fill color applied as a result of conditional
formatting. Once againg, suggestions are appreciated.

 

[GS]

Kay presented the following explanation :

Gary,

Great question, but the odd thing is that each product has a different range
of acceptable targets.

It sounds to me like you should go with a VB solution. CF is only
useful when the sheet is designed for expected data in expected cells.

Thus, the conditional format references cell
addresses rather than values. =AND(TRIM($J6)<>"",$J6<$M6).

I see BOTH addresses AND values here, and which is what I expect to see

While the majority
of records could have a green status if one record has a red status, the
scorecard must show red. I was thinking maybe you could reference the color
index number. Something like...colorindex.

I don't think that's possible with CF, and so further suggests a VB
solution is in order.

In fact according to Chip Pearson's site, you can with his VB Module.
I have imported that and successfully tested on cells that I apply direct
fill color to. However, I can't get this to work with the fill color
applied as a result of conditional formatting.
Once againg, suggestions are appreciated.

Well, it sounds like you should stick with Chip's solution since it
fits your scenario. Does this change the fill in cells OR does it set
CF on the target cell? (I'm not familiar with Chip's VB solution!)

As I said, CF is only useful on sheets where the expected data is put
into the expected cells. I'm not sure why you can't get this to work as
a result of CF since I can't see your file here. As long as the
criteria is what drives the CF in the form of a formula it should work.
Your sample CF condition looks like it should work fine.

regards,