PC Review


Reply
Thread Tools Rate Thread

"Daisy-Chain" a conditional format?

 
 
=?Utf-8?B?c2l4d2VzdA==?=
Guest
Posts: n/a
 
      12th May 2006
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
 
Reply With Quote
 
 
 
 
Bernard Liengme
Guest
Posts: n/a
 
      12th May 2006
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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



 
Reply With Quote
 
 
 
 
5631
Guest
Posts: n/a
 
      12th May 2006

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 With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple conditional on conditional format formula Frank Kabel Microsoft Excel Programming 1 27th Jul 2004 06:24 PM
Re: Multiple conditional on conditional format formula Bob Phillips Microsoft Excel Programming 0 27th Jul 2004 05:30 PM
Conditional Formatting w/ Conditional Functions... using AND =?Utf-8?B?S1dHU05B?= Microsoft Excel Programming 2 16th Mar 2004 07:56 PM
Conditional, Conditional Formatting John Meyer Microsoft Excel Discussion 8 21st Dec 2003 09:37 PM
Conditional Compilation/Conditional Constants chris han Microsoft C# .NET 1 10th Jul 2003 09:50 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:35 PM.