Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing conditional format is another workbookHI
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing conditional format is another workbookHI
[You need to copy the cell with the format then use paste special using fORMATS to paste the cell into the the sedcond workbook To get format only: Range("A1").Copy Range("C1").PasteSpecial _ Paste:=xlPasteFormats Or to get the value and the format Range("A1").Copy Range("C1").PasteSpecial _ Paste:=xlPasteFormats Range("C1").PasteSpecial _ Paste:=xlPasteValues The source and destination cells can be any cell in any workbook. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207258 http://www.thecodecage.com/forumz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing conditional format is another workbookHI
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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing conditional format is another workbookHI
I think you need to read the two articale Chip Pearson has under "Conditional formating". Conditional formating contains formulas which can refer to just the cell contents that you are copying or references to toher cells. depedning on the reference cells in the condional formating would depend if you can just copy the coditional formating or would need to modfiy the conditional formating. I assumed incorrectly that you wouldn't need to modify the formulas. Chip's webpage shows how to programmable work with conditional formats. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207258 http://www.thecodecage.com/forumz |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing conditional format is another workbookHI
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. "Kay" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing conditional format is another workbookHI
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. "Kay" wrote: 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. $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, -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing conditional format is another workbook using Chip Pear
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. "Kay" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing conditional format is another workbook using Chip Pear
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 since CF acts on cell addresses according to values as the criteria.<g 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[s]? (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, -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel, conditional format (font red) referencing another cell? | Excel Worksheet Functions | |||
VBA adding Conditional Format to Range referencing another column value | Excel Programming | |||
Conditional Formatting: Referencing from a different cell | Excel Worksheet Functions | |||
How do I conditional format by referencing failure/success? | Excel Worksheet Functions | |||
Help with conditional formatting and referencing workbooks... | Excel Programming |