"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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Guest

    copy conditional format as ACTUAL format

    Guest, Dec 7, 2004, in forum: Microsoft Excel Misc
    Replies:
    7
    Views:
    12,750
    Dave Peterson
    Dec 7, 2004
  2. Guest

    Conditional Format - Format Transfer To Chart

    Guest, Jun 1, 2006, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    300
    Guest
    Jun 1, 2006
  3. tmbo
    Replies:
    1
    Views:
    396
    Guest
    Aug 23, 2006
  4. Guest

    custom format or conditional format?

    Guest, Oct 24, 2006, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    242
    Guest
    Oct 24, 2006
  5. Guest

    Conditional Format (not color format)

    Guest, Nov 22, 2006, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    244
    Guest
    Nov 22, 2006
  6. Brian

    Conditional Format & Default Format Help

    Brian, Jan 31, 2008, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    236
    David Biddulph
    Jan 31, 2008
  7. Rene
    Replies:
    3
    Views:
    642
    David Biddulph
    Feb 27, 2008
  8. LiveUser

    Help with format painter and conditional format

    LiveUser, Mar 13, 2008, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    439
    LiveUser
    Mar 13, 2008
Loading...