Formula Help

[s boak]

Hi Folks:

We have a simple 1-page sheet with a resources we share.
Columns are:
A Date
B Day
C User 1
D User 2
E User 3
A need is indicated by simply placing an XX in a cell

My question:
If User 1 needs the resource on a specific day (let's say E35), how can I
prevent somebody filling in F35 or G35 ??

Many thanks
Steve

 

[hcj]

You can't PREVENT them from filling in, but you can signal
that another user is signed up. Do this:
Use conditional formatting in each user column as
follows; adjust column refs as needed - my example has
user 1 in column A, user 2 in column B and user 3 in
column C, starting in row 2:

1. In column A format as

Formula is =OR(NOT(ISBLANK(B2)),NOT(ISBLANK(C2)))
with format pattern is black.

This will blacken in columns B and C if anything is
entered in column A.

2. In column B format as

Formula is =OR(NOT(ISBLANK(A2)),NOT(ISBLANK(C2)))
with format pattern is black
This will blacken in columns A and C is anything is
entered in column B.

3. In column C format as

Formula is =OR(NOT(ISBLANK(A2)),NOT(ISBLANK(B2)))
with format pattern is black

If you have more than three users, you have to reference
each of their columns in each of the =OR statements.

Hope this helps.

 

[hcj]

Sorry - I mispoke - first formatting will blacken Column A
if anything is entered in columns B or C, etc. Works
fine, I just mispoke.

hcj