A dilly of a question!

  • Thread starter Thread starter Connie
  • Start date Start date
C

Connie

I hope I can put my question forth clearly and briefly.
Here goes! First of all, I'm working with Excel 2000.

- I have a workbook with 3 worksheets.
- If data is entered in D16 on Sheet 1, I want D16 on
Sheets 2 and 3 disabled so they can't be typed in.
- If data is entered in D16 on Sheet 2, I want D16 on
Sheets 1 and 3 disabled so they can't be typed in.
- If data is entered in D16 on Sheet 3, I want D16 on
Sheets 1 and 2 disabled so they can't be typed in.

Is this possible?
 
Connie,

You will need to create a named range called Test
(anywhere in your workbook is fine).*

In Test put the formula:
=Sheet1!D16 & Sheet2!D16 & Sheet3!D16

Select Sheet1!D16 and from the menu choose
Data -> Validation then
Under the Settings tab choose Allow Custom and in
the Formula field put "=D16=Test" without quotes
Under the Error Alert tab enter a custom error
message if you choose or use excel's default.

Repeat for each sheet (2 and 3)

Then a user can only have 1 of either Sheet1!D16,
Sheet2!D16 or Sheet3!D16

* To create a named range from the menu select
Insert -> Name -> Define
Put in Test and set it to whichever cell you choose.
This allows the use of 1 cell in all data validation.

Dan E
 
Dan, thank you very much. I will look forward to
incorporating this into the workbook when I'm back in the
office Monday. Connie

-----Original Message-----
Connie,

You will need to create a named range called Test
(anywhere in your workbook is fine).*

In Test put the formula:
=Sheet1!D16 & Sheet2!D16 & Sheet3!D16

Select Sheet1!D16 and from the menu choose
Data -> Validation then
Under the Settings tab choose Allow Custom and in
the Formula field put "=D16=Test" without quotes
Under the Error Alert tab enter a custom error
message if you choose or use excel's default.

Repeat for each sheet (2 and 3)

Then a user can only have 1 of either Sheet1!D16,
Sheet2!D16 or Sheet3!D16

* To create a named range from the menu select
Insert -> Name -> Define
Put in Test and set it to whichever cell you choose.
This allows the use of 1 cell in all data validation.

Dan E

"Connie" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Back
Top