data validation

[Gary Adamson]

Just to make it simpler I made a sheet that in columns A1
through A4 there is data validation that reads columns A5
thru A8 for input.

In column A5 I put the formula:

=IF(COUNTIF(A$1:A4,1)>0,IF(COUNTIF(A$1:A4,2)>0,IF(COUNTIF
(A$1:A4,3)>0,IF(COUNTIF(A$1:A4,4)>0,"",4),3),2),1)

I then dragged this down to column A8.

So when a number is used it gets erased from the list, so
that there is only a choice to use the other numbers that
are left.

This works fine, except when the number is entered
manually and not from the drop down list.
It seems that when the number is typed in, the cell
references change accordingly and so the number is not in
the list by the time the validation error occurs.

Is there a way around this so that I could either choose
from the list or manually type the number in?

 

[Frank Kabel]

Hi
see:
http://www.contextures.com/xlDataVal03.html

 

[Gary Adamson]

Unfortunately, the same thing happens when I try it that
way as well.
If you try to enter the item manually and not from the
drop down list, the cell references change accordingly and
so the number is not in the list by the time the
validation error occurs.