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