Conditional formatting confusion

K

Keith

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
 
M

Martin Fishlock

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
 
M

Max

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(Keystone,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(Keystone,ROW(A1),0))=3)
Format: Yellow fill

Cond 3:
=AND(N(OFFSET(Keystone2,ROW(A1),0))=1,N(OFFSET(Keystone,ROW(A1),0))>3)
Format: Green fill
 
M

Martin Fishlock

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 said:
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(Keystone,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(Keystone,ROW(A1),0))=3)
Format: Yellow fill

Cond 3:
=AND(N(OFFSET(Keystone2,ROW(A1),0))=1,N(OFFSET(Keystone,ROW(A1),0))>3)
Format: Green fill

Keith said:
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
Click to expand...
Click to expand...
 
M

Max

Pleasure`, Martin.
Cheers

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.....)
Click to expand...
 
K

Keith

Many thanks to Max and Martin!
Best,
keith

Martin Fishlock said:
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 said:
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(Keystone,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(Keystone,ROW(A1),0))=3)
Format: Yellow fill

Cond 3:
=AND(N(OFFSET(Keystone2,ROW(A1),0))=1,N(OFFSET(Keystone,ROW(A1),0))>3)
Format: Green fill

Keith said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

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. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Easier Match/Offset? 12
Deleting specific information 1
Match & IF 1
Trying To Put IFs into a Function 3
Mapping integers to strings 6
Copying a Conditional Formatting Formula 2
Event recognition. 11
Ambiguous name detected: worksheet_change 2

Top