Name Range + Data Validation

[Connie]

Hi, Please see the posts below before reading my current
problem.

CURRENT PROBLEM
It worked dandy with one cell. But now I'm wanting to
extend this to cover several columns and rows. I have
created seven name ranges, calling them Test, Test2,
Test3, etc. I have validated all cells. But there are
many cells here and there that I can't type in because
it's picking it up as a cell already typed in on another
sheet, but it's not! Any suggestions?

MY FIRST POST

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?

THE ANSWER
Subject: Re: A dilly of a question!
From: "Dan E" <[email protected]>
Sent: 11/7/2003 2:09:31 PM


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