In Excel 2007, I am trying to apply conditional formatting based on a formula
which basically says "if a1 in sheet1 contains a number greater than 10, make
b1 in sheet2 red."

Firstly, I see that you Excel doesn't like using references to other
worksheets for conditional formatting criteria. I have tried creating a
named range and also copied the cells into the same worksheet (so they are
now linked) but it doesn't seem to like any of my attempts! I am probably
not using the right formula.

Any tips, advice and help would be most appreciated.

If you name a1 in sheet1 say criterium then for B1 in sheet2 this is the
formula:
=criterium 10

This is so simple, I don't think you made an error in it. I guess that your
real situation is more complex, please post exact ranges you use, and exact
criteria which describe the job not only "basically" but in full details!

Regards,
Stefi

€žClaire U€� ezt Ã*rta:

In Excel 2007, I am trying to apply conditional formatting based on a formula
which basically says "if a1 in sheet1 contains a number greater than 10, make
b1 in sheet2 red."

Firstly, I see that you Excel doesn't like using references to other
worksheets for conditional formatting criteria. I have tried creating a
named range and also copied the cells into the same worksheet (so they are
now linked) but it doesn't seem to like any of my attempts! I am probably
not using the right formula.

Any tips, advice and help would be most appreciated.

Thank you Stefi - I was trying to make it so much more complicated than it
needed to be!

"Claire U" wrote:

In Excel 2007, I am trying to apply conditional formatting based on a formula
which basically says "if a1 in sheet1 contains a number greater than 10, make
b1 in sheet2 red."

Firstly, I see that you Excel doesn't like using references to other
worksheets for conditional formatting criteria. I have tried creating a
named range and also copied the cells into the same worksheet (so they are
now linked) but it doesn't seem to like any of my attempts! I am probably
not using the right formula.

Any tips, advice and help would be most appreciated.

You are welcome! Thanks for the feedback!
Stefi

€žClaire U€� ezt Ã*rta:

Thank you Stefi - I was trying to make it so much more complicated than it
needed to be!

"Claire U" wrote:

In Excel 2007, I am trying to apply conditional formatting based on a formula
which basically says "if a1 in sheet1 contains a number greater than 10, make
b1 in sheet2 red."

Firstly, I see that you Excel doesn't like using references to other
worksheets for conditional formatting criteria. I have tried creating a
named range and also copied the cells into the same worksheet (so they are
now linked) but it doesn't seem to like any of my attempts! I am probably
not using the right formula.

Any tips, advice and help would be most appreciated.

Try this:

=AND(COUNT(INDIRECT("Sheet1!A1")),INDIRECT("Sheet1 !A1")10)

Or, you could give cell Sheet1 A1 a name, say, Sh1A1, then:

=AND(COUNT(Sh1A1),Sh1A110)

--
Biff
Microsoft Excel MVP


"Claire U" wrote in message
...
In Excel 2007, I am trying to apply conditional formatting based on a
formula
which basically says "if a1 in sheet1 contains a number greater than 10,
make
b1 in sheet2 red."

Firstly, I see that you Excel doesn't like using references to other
worksheets for conditional formatting criteria. I have tried creating a
named range and also copied the cells into the same worksheet (so they are
now linked) but it doesn't seem to like any of my attempts! I am probably
not using the right formula.

Any tips, advice and help would be most appreciated.