"Daisy-Chain" a conditional format?

Discussion in 'Microsoft Excel Misc' started by Guest, May 12, 2006.

  1. Guest

    Guest Guest

    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
     
    Guest, May 12, 2006
    #1
    1. Advertisements

  2. 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
    news:...
    >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
     
    Bernard Liengme, May 12, 2006
    #2
    1. Advertisements

  3. Guest

    5631 Guest

    5631, May 12, 2006
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    copy conditional format as ACTUAL format

    Guest, Dec 7, 2004, in forum: Microsoft Excel Misc
    Replies:
    7
    Views:
    9,222
    Dave Peterson
    Dec 7, 2004
  2. Guest
    Replies:
    3
    Views:
    169
    Guest
    Aug 20, 2005
  3. Guest

    Conditional Format - Format Transfer To Chart

    Guest, Jun 1, 2006, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    188
    Guest
    Jun 1, 2006
  4. tmbo
    Replies:
    1
    Views:
    183
    Guest
    Aug 23, 2006
  5. Rene
    Replies:
    3
    Views:
    452
    David Biddulph
    Feb 27, 2008
Loading...

Share This Page