Preserve formula in cell

[Weatherall Ian D.]

Hello All,
I am creating a scheduling workbook with 2 work sheets, the first sheet is
the schedule itself and the second is the availability. When a staff member
is not available to work a particular day, I enter N/A into sheet2. In
sheet1 a simple if statement displays N/A when the corresponding cell in
sheet2 is N/A.

eg.

sheet1!a1=if(sheet2!a1="N/A","N/A","")

So here is the question, when I enter a start time in sheet1!a1 the formula
get's replaced.
Is it possible to enter a start time without replacing the formula.

In addition I want to use conditional formatting, to check if the start time
I enter in sheet 1 isn't earlier than the start time in sheet 2.

regards,
Ian

 

[Anon]

Hello All,
I am creating a scheduling workbook with 2 work sheets, the first sheet is
the schedule itself and the second is the availability. When a staff member
is not available to work a particular day, I enter N/A into sheet2. In
sheet1 a simple if statement displays N/A when the corresponding cell in
sheet2 is N/A.

eg.

sheet1!a1=if(sheet2!a1="N/A","N/A","")

So here is the question, when I enter a start time in sheet1!a1 the formula
get's replaced.
Is it possible to enter a start time without replacing the formula.

No. A cell can contain a constant (data) or a formula, but not both.
It seems to me you are unnecessarily complicating the situation by having
two sheets for data entry. Why not just enter either start time or N/A in
one sheet?
If you need a separate sheet just showing availability, then make a sheet2
with formulas that look for N/A in sheet1.
Keep the structure simple. Have one area for data entry and another for
calculations based on that data. Don't try to 'fold back'.