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