Using a formula in cell with a drop-down Box

[TheThib]

I am developing a program to track my work schedule that consists of a
couple of worksheets.

For simplicity:

In WORKSHEET 1 I have a simple layout with one cell dedicated to each
day of the month. Each cell has a drop-down box that allows me to
select ON or OFF, relating to whether I am scheduled to work or not.
and each cell has a For each peron that I am scheduling,

In WORKSHEET 2 I have two cells dedicated to each day of the month.
Each cell has a drop-down box that allows me to select a starting time
in one and an ending time in the other.

What I am trying to do is to start in WORKSHEET 1 and select my "ON"
and "OFF" days.

If a particular day on WORKSHEET 1 is selected as ON then there is no
change in the related corresponding cells for that day in WORKSHEET 2
and I am able to select my starting and ending times on that second
sheet.

If, however I select OFF then I would like both of the corresponding
cells in the second sheet to display OFF in the related cells.

Since my schedule changes, I would like to be able to have an "OFF"
selection made in WKSHET 1 override the time selection in WKSHET2 and
vicea versa, when I select from "OFF" to "ON", the times would be
available for me to select from.

Any suggestions would be most apprecitated.

 

[Otto Moehrbach]

One way:
Use a Worksheet_Change macro in the first sheet to pick up on the OFF. That
macro would then call another macro (if the OFF is there) to override the
Data Validation in the corresponding cells of the second sheet and insert
OFF there. The reverse if you change the OFF to ON.
HTH Otto

 

[TheThib]

Thanks Otto - Merry Christmas!