Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Daisy-Chain" a conditional format?
I wasn't sure if setting a dynamic range would work in this setup so I tried
something novel (the fact that I'm writing this means it didn't work!). Here goes: On sheet1, say column"A", I have a data list in each cell where the only choices are "Y" and "Pndg." On sheet2, I have a match setup where for example cell A1 has the formula "=Sheet1!A1" where the "Y" or "Pndg." will automatically propagate when selected on sheet1. So far so good. NOW, on sheet2 I have conditional formats setup in column A where those cells will either "go green" on a "Y" propagation or "go yellow" on a "Pndg." propagation. Again, so far so good. What I'm trying to do: I'm setting up something I call "phantom cells" on sheet2, column "B" where the data is hidden by matching the cell pattern color with the font color (such as "white on white"). In this column I want a "1" to propagate for all the corresponding cells (A1-B2,A2-B2,etc...) in column "A where a "Y" (and then subsequently a green pattern) exist. What I obviously want to do then is SUM column "B" on sheet 2 (all the "1"'s) so all the "green cells" in column A give me a running total. The frustrating thing is that I'm inputting the correct conditional "IF" formula on sheet2 ("=IF(A1="Y",1)). I don't get a formula error but it doesn't work. Is the problem that it's indirectly referencing another sheet? Thanks for all replies! -- 6-West |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Daisy-Chain" a conditional format?
I am not sure why you told us about the conditional formatting.
However, if I use in A1 of Sheet2 the formula =Sheet1!A1, and in B1 =IF(A1="Y",1,"") I get 1 when Y is displayed in A1 of Sheet2. You said "it doesn't work" but you did not tell what did happen. Presumably with your formula you get FALSE. I can get this behaviour if on Sheet1 cell A1 has not "Y" but "Y " - the letter is followed by a space. To get my IF to work I used =TRIM(Sheet1!A1) on Sheet2. By the way, to count the Y values I can also use =COUNTIF(A1:A12,"Y") best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "sixwest" wrote in message ... I wasn't sure if setting a dynamic range would work in this setup so I tried something novel (the fact that I'm writing this means it didn't work!). Here goes: On sheet1, say column"A", I have a data list in each cell where the only choices are "Y" and "Pndg." On sheet2, I have a match setup where for example cell A1 has the formula "=Sheet1!A1" where the "Y" or "Pndg." will automatically propagate when selected on sheet1. So far so good. NOW, on sheet2 I have conditional formats setup in column A where those cells will either "go green" on a "Y" propagation or "go yellow" on a "Pndg." propagation. Again, so far so good. What I'm trying to do: I'm setting up something I call "phantom cells" on sheet2, column "B" where the data is hidden by matching the cell pattern color with the font color (such as "white on white"). In this column I want a "1" to propagate for all the corresponding cells (A1-B2,A2-B2,etc...) in column "A where a "Y" (and then subsequently a green pattern) exist. What I obviously want to do then is SUM column "B" on sheet 2 (all the "1"'s) so all the "green cells" in column A give me a running total. The frustrating thing is that I'm inputting the correct conditional "IF" formula on sheet2 ("=IF(A1="Y",1)). I don't get a formula error but it doesn't work. Is the problem that it's indirectly referencing another sheet? Thanks for all replies! -- 6-West |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Daisy-Chain" a conditional format?
By the way , conditional formatting works if you refer to names. Say, you have a region "A1" on the Sheet1 called "Region1". You can refer to "Region1" in your cond. formatting formula on the Sheet2. But you can't do the same with "A1". -- 5631 ------------------------------------------------------------------------ 5631's Profile: http://www.excelforum.com/member.php...o&userid=33022 View this thread: http://www.excelforum.com/showthread...hreadid=541517 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional format of minimum number | Excel Worksheet Functions | |||
Conditional Format Question | Excel Worksheet Functions | |||
conditional format of data tables in charts | Charts and Charting in Excel | |||
Draging a conditional format | Excel Worksheet Functions | |||
Conditional format | Excel Discussion (Misc queries) |