I need to conditionally format based on the contents of two other
worksheets, so I've created named ranges. Since I have to do a whole bunch
of cells, I figured I'd make two single-cell named ranges and just use an
offset to get the two relative values for each cell I need to format.

Here are my three conditions
1. (Green): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) 3))
2. (Yellow): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) =3))
3. (Red): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) 0))

(once I get this working, I'll add a column offset into the second half of
each condition as well)

To test, I made sure that both parts of condition1 evaluate to True by
putting it in cells in the target worksheet;
=(OFFSET(Keystone2,ROW(D9)-8,0) = 1) = True
=(OFFSET(Keystone,ROW(D9)-8,0) 3) = True

but my cell remains uncolored, when I think that it should be green when
both these conditions are true.

I'm sure I'm missing something simple, but I'm not sure what. Any ideas?

Thanks,
Keith

Hi Keith:

The AND was causing problems so try multiplying the booleans note you may
need to put double negatives (--) before the offset:

=(OFFSET(Keystone2,ROW(D9)-8,0)=1)*(OFFSET(Keystone,ROW(D9)-8,0)3)=1

Note you may find it better to remove the cell reference in the row and use
the current cell as the D9 will be fixed as in:

=(OFFSET(Keystone2,ROW()-8,0)=1)*(OFFSET(Keystone,ROW()-8,0)3)=1

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Keith" wrote:

I need to conditionally format based on the contents of two other
worksheets, so I've created named ranges. Since I have to do a whole bunch
of cells, I figured I'd make two single-cell named ranges and just use an
offset to get the two relative values for each cell I need to format.

Here are my three conditions
1. (Green): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) 3))
2. (Yellow): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) =3))
3. (Red): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) 0))

(once I get this working, I'll add a column offset into the second half of
each condition as well)

To test, I made sure that both parts of condition1 evaluate to True by
putting it in cells in the target worksheet;
=(OFFSET(Keystone2,ROW(D9)-8,0) = 1) = True
=(OFFSET(Keystone,ROW(D9)-8,0) 3) = True

but my cell remains uncolored, when I think that it should be green when
both these conditions are true.

I'm sure I'm missing something simple, but I'm not sure what. Any ideas?

Thanks,
Keith

I corrected your condition for Red [ think it should also contain an
additional "<3" check for OFFSET(Keystone2,...) to plug the gap ], and
re-sequenced the 3 CF conditions to make it a smoother progressive
evaluation. I also threw in an N function wrap around the OFFSET(...), eg:
N(OFFSET(...)) to help stir the CF functionality to life <g

It works ok here when I applied the CF as below:

Cond 1:
=AND(N(OFFSET(Keystone2,ROW(A1),0))=1,N(OFFSET(Key stone,ROW(A1),0))0,N(OFFSET(Keystone,ROW(A1),0))< 3)
Format: Red fill

Cond 2:
=AND(N(OFFSET(Keystone2,ROW(A1),0))=1,N(OFFSET(Key stone,ROW(A1),0))=3)
Format: Yellow fill

Cond 3:
=AND(N(OFFSET(Keystone2,ROW(A1),0))=1,N(OFFSET(Key stone,ROW(A1),0))3)
Format: Green fill
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Keith" wrote:
I need to conditionally format based on the contents of two other
worksheets, so I've created named ranges. Since I have to do a whole bunch
of cells, I figured I'd make two single-cell named ranges and just use an
offset to get the two relative values for each cell I need to format.

Here are my three conditions
1. (Green): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) 3))
2. (Yellow): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) =3))
3. (Red): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) 0))

(once I get this working, I'll add a column offset into the second half of
each condition as well)

To test, I made sure that both parts of condition1 evaluate to True by
putting it in cells in the target worksheet;
=(OFFSET(Keystone2,ROW(D9)-8,0) = 1) = True
=(OFFSET(Keystone,ROW(D9)-8,0) 3) = True

but my cell remains uncolored, when I think that it should be green when
both these conditions are true.

I'm sure I'm missing something simple, but I'm not sure what. Any ideas?

Thanks,
Keith

Slight correction ..

Line:
.. an additional "<3" check for OFFSET(Keystone2,...) ..

should have read as:
.. an additional "<3" check for OFFSET(Keystone,...) ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks Max for bringing that one to life for me.

I could get the offsets to work without the and but not with an and so use
the N function or as I did just multily them together (ok for an and but not
as nice for adding for an or.....)

--

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Max" wrote:

I corrected your condition for Red [ think it should also contain an
additional "<3" check for OFFSET(Keystone2,...) to plug the gap ], and
re-sequenced the 3 CF conditions to make it a smoother progressive
evaluation. I also threw in an N function wrap around the OFFSET(...), eg:
N(OFFSET(...)) to help stir the CF functionality to life <g

It works ok here when I applied the CF as below:

Cond 1:
=AND(N(OFFSET(Keystone2,ROW(A1),0))=1,N(OFFSET(Key stone,ROW(A1),0))0,N(OFFSET(Keystone,ROW(A1),0))< 3)
Format: Red fill

Cond 2:
=AND(N(OFFSET(Keystone2,ROW(A1),0))=1,N(OFFSET(Key stone,ROW(A1),0))=3)
Format: Yellow fill

Cond 3:
=AND(N(OFFSET(Keystone2,ROW(A1),0))=1,N(OFFSET(Key stone,ROW(A1),0))3)
Format: Green fill
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Keith" wrote:
I need to conditionally format based on the contents of two other
worksheets, so I've created named ranges. Since I have to do a whole bunch
of cells, I figured I'd make two single-cell named ranges and just use an
offset to get the two relative values for each cell I need to format.

Here are my three conditions
1. (Green): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) 3))
2. (Yellow): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) =3))
3. (Red): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) 0))

(once I get this working, I'll add a column offset into the second half of
each condition as well)

To test, I made sure that both parts of condition1 evaluate to True by
putting it in cells in the target worksheet;
=(OFFSET(Keystone2,ROW(D9)-8,0) = 1) = True
=(OFFSET(Keystone,ROW(D9)-8,0) 3) = True

but my cell remains uncolored, when I think that it should be green when
both these conditions are true.

I'm sure I'm missing something simple, but I'm not sure what. Any ideas?

Thanks,
Keith

Pleasure`, Martin.
Cheers
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Feb 5, 10:09 pm, Martin Fishlock
wrote:
Thanks Max for bringing that one to life for me.

I could get the offsets to work without the and but not with an and so use
the N function or as I did just multily them together (ok for an and but not
as nice for adding for an or.....)

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Many thanks to Max and Martin!
Best,
keith

"Martin Fishlock" wrote in message
...
Thanks Max for bringing that one to life for me.

I could get the offsets to work without the and but not with an and so use
the N function or as I did just multily them together (ok for an and but
not
as nice for adding for an or.....)

--

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Max" wrote:

I corrected your condition for Red [ think it should also contain an
additional "<3" check for OFFSET(Keystone2,...) to plug the gap ], and
re-sequenced the 3 CF conditions to make it a smoother progressive
evaluation. I also threw in an N function wrap around the OFFSET(...),
eg:
N(OFFSET(...)) to help stir the CF functionality to life <g

It works ok here when I applied the CF as below:

Cond 1:
=AND(N(OFFSET(Keystone2,ROW(A1),0))=1,N(OFFSET(Key stone,ROW(A1),0))0,N(OFFSET(Keystone,ROW(A1),0))< 3)
Format: Red fill

Cond 2:
=AND(N(OFFSET(Keystone2,ROW(A1),0))=1,N(OFFSET(Key stone,ROW(A1),0))=3)
Format: Yellow fill

Cond 3:
=AND(N(OFFSET(Keystone2,ROW(A1),0))=1,N(OFFSET(Key stone,ROW(A1),0))3)
Format: Green fill
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Keith" wrote:
I need to conditionally format based on the contents of two other
worksheets, so I've created named ranges. Since I have to do a whole
bunch
of cells, I figured I'd make two single-cell named ranges and just use
an
offset to get the two relative values for each cell I need to format.

Here are my three conditions
1. (Green): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) 3))
2. (Yellow): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) =3))
3. (Red): =AND((OFFSET(Keystone2,ROW(D9)-8,0) =
1),(OFFSET(Keystone,ROW(D9)-8,0) 0))

(once I get this working, I'll add a column offset into the second half
of
each condition as well)

To test, I made sure that both parts of condition1 evaluate to True by
putting it in cells in the target worksheet;
=(OFFSET(Keystone2,ROW(D9)-8,0) = 1) = True
=(OFFSET(Keystone,ROW(D9)-8,0) 3) = True

but my cell remains uncolored, when I think that it should be green
when
both these conditions are true.

I'm sure I'm missing something simple, but I'm not sure what. Any
ideas?

Thanks,
Keith

Thanks for posting back
Trust you got the issue resolved since ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Feb 6, 9:56 pm, "Keith"
wrote:
Many thanks to Max and Martin!
Best,
keith