Data Validation

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

Connie

I have posted this question, worded differently, before in
this newsgroup and have tried to follow the instructions
but I'm not getting the results I would like, so I will
try again. Maybe I'm asking something impossible, I don't
know.

Excel 2000, with a workbook containing three worksheets.
The cells from D16 to P57 can only be typed in on one of
the three sheets---D16, for example, cannot be completed
on more than one worksheet. Once typed in on one
worksheet, I would like the corresponing cell on the other
two worksheets disabled, preferrably by Data Validation.
I don't want a macro that automatically deletes
duplication because this will confuse the user. When the
user attempts to complete cell D16 on one sheet and then
tries to enter information in D16 on another sheet, I want
an error message coming up saying this cell has already
been completed on another sheet, etc. I can only see Data
Validation doing that.

First of all, I followed previous instructions to create a
named range called Test. (If your name is "Dan E", you
will recognize this!) In the first cell of that range I
typed the formula =Sheet1!D16 & Sheet2!D16 & Sheet3!D16.
I dragged that down to D57. I created a second range
called Test2, and typed in =Sheet1!E16 & Sheet2!E16 &
Sheet3!E16, and dragged it down to E57, and so on until I
had created enough test ranges to reach the P colum.

In each cell then from D16 to P57 on all three worksheets,
I did a data validation (Custom) with the
formula "=D16=Test" (without the quotes) or "=E16=Test" or
=F16=Test2", etc., and dragged each one down.

This isn't working. I can type in D16 on all three
worksheets. So, I am assuming that the formula I'm using
in Data Validation isn't correct.

This is too long, I know. Please excuse. But if you've
read to here, you must be half-way interested in my
problem!! :-)

Connie
 
In the top left cell of the Test range, enter the following formula:

=COUNTA(Sheet1:Sheet3!D16)

Copy this down to row 57, then copy across to column P

On sheet1, select cells D16:P57.
Choose Data>Validation
Select Custom, and enter the following formula:

=INDEX(Test,ROW()-15,COLUMN()-3)=0
Click OK

Copy sheet1!D16:P57
Select Sheet2!D16:P57 and choose Edit>Paste Special
Select Validation, click OK

Select Sheet3!D16:P57 and choose Edit>Paste Special
Select Validation, click OK
 
Thank you for replying. You're on to what I'm trying to
do but there's one little thing amiss, and that is, I
can't type in any of the cells now, even when the cells
have not been completed on another sheet. Any
suggestions? We seem so close to getting this, I hate to
give up, but I'm at a loss as to what to do.

Connie
 
Just one further note. In the Test range there's a one in
every cell, but there's absolutely nothing in those cells
on the worksheets!
 
Maybe there's a space character on one of the sheets. On each sheet,
select the range D16:P57, and press the Delete key.

Does that change the cells in the Test range to zero?
 
No, it didn't change anything. There remains a one in
every cell in the Test range. In the worksheet some of
the columns contain dropdown menus, but nothing selected.
However, by doing what you mentioned, the Test range is
still indicating there's something already in all the
cells.
 
Are the sheets arranged Sheet1, Sheet2, Sheet3, without any other sheets
in between?

If there are other sheets interspersed, you could change the formula in
the test range to:
=COUNTA(Sheet1!D16)+COUNTA(Sheet2!D16)+COUNTA(Sheet3!D16)
 
There are no sheets in between, however, Sheet 3 is
different than one and two. But, as an experiment, I
deleted columns in the test range that did not pertain to
Sheet 3. Nothing changes. There's still a 1 in every
cell of the test range.
 
If you'd like me to take a look at it, you can email it to me (remove
the capital letters from my email address).
 
I have left a message with our Director to see if it's
okay to e-mail this out. I will let you know. Thank you.
 
Back
Top