I like to format a column such that it allows only distinct values. for
eg in the column A i have 10 numbers 1to 10 (A1 to A10) . if i enter
any value between 1 and 10 in A11 it should not allow. A11,A12.....etc
should allow only distinct values. pls help me to do this

thanks in advance


--
parthaemail
------------------------------------------------------------------------
parthaemail's Profile: http://www.excelforum.com/member.php...o&userid=31311
View this thread: http://www.excelforum.com/showthread...hreadid=519287

Data Validation Allow whole number Between 1 and 10...add error
message if you wish, according to menu.

Vaya con Dios,
Chuck, CABGx3



"parthaemail" wrote:


I like to format a column such that it allows only distinct values. for
eg in the column A i have 10 numbers 1to 10 (A1 to A10) . if i enter
any value between 1 and 10 in A11 it should not allow. A11,A12.....etc
should allow only distinct values. pls help me to do this

thanks in advance


--
parthaemail
------------------------------------------------------------------------
parthaemail's Profile: http://www.excelforum.com/member.php...o&userid=31311
View this thread: http://www.excelforum.com/showthread...hreadid=519287

hi

the below procedure is not working out, can you please check it out




Data Validation Allow whole number Between 1 and 10...add error
message if you wish, according to menu.

Vaya con Dios,
Chuck, CABGx3


--
parthaemail
------------------------------------------------------------------------
parthaemail's Profile: http://www.excelforum.com/member.php...o&userid=31311
View this thread: http://www.excelforum.com/showthread...hreadid=519287

hi!

Data Validation Allow Whole Numbers Not Between 1 & 10

-via135



parthaemail Wrote:
hi

the below procedure is not working out, can you please check it out




Data Validation Allow whole number Between 1 and 10...add error
message if you wish, according to menu.

Vaya con Dios,
Chuck, CABGx3


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=519287

why not put a list somewhere 1,23,...10 for e.g in g1 to g10
in data validation window choose list(in settings)
and give g1:g10 and error message as not valid.


"parthaemail"
wrote in message
...

hi

the below procedure is not working out, can you please check it out




Data Validation Allow whole number Between 1 and 10...add error
message if you wish, according to menu.

Vaya con Dios,
Chuck, CABGx3


--
parthaemail
------------------------------------------------------------------------
parthaemail's Profile:
http://www.excelforum.com/member.php...o&userid=31311
View this thread: http://www.excelforum.com/showthread...hreadid=519287

Do you wish to enter UNIQUE values into column A?
If yes, then I think 'Data Validation' will not work.

A clumsy approach would be:
In another column, say B.
In B1 =IF(MAX(COUNTIF(A:A,A:A))1,"Duplicate","")
Copy this formula down column b as far as you wish.

If you have '8' in A8, and enter '8' in say A12, then
A8 and A12 will display 'Duplicate', until you change one
of the '8' entries.

Any use?

George Gee


"parthaemail"
wrote in message
...

hi

the below procedure is not working out, can you please check it out




Data Validation Allow whole number Between 1 and 10...add error
message if you wish, according to menu.

Vaya con Dios,
Chuck, CABGx3

Sorry, slight mistake, should read:
B8 and B12 will display 'Duplicate'.....

George Gee


"George Gee" wrote in message
...


If you have '8' in A8, and enter '8' in say A12, then
A8 and A12 will display 'Duplicate', until you change one
of the '8' entries.

Sorry, you're right of course..........I mis-read the post. I see others
have since given you answers more to the point.

Vaya con Dios,
Chuck, CABGx3



"parthaemail" wrote:


hi

the below procedure is not working out, can you please check it out




Data Validation Allow whole number Between 1 and 10...add error
message if you wish, according to menu.

Vaya con Dios,
Chuck, CABGx3


--
parthaemail
------------------------------------------------------------------------
parthaemail's Profile: http://www.excelforum.com/member.php...o&userid=31311
View this thread: http://www.excelforum.com/showthread...hreadid=519287

Goto cell A then choose 'Data Validation' from the menu

In the Allow: box choose 'custom'
In the formula: box type the following
=AND(A1=1,A1<=10,MOD(A1,1)=0,FREQUENCY(A:A,A:A)<= 1)
Enter an appropriate error message under the error alert tab
Then copy down to as many cells as needed

The mod function checks for whole numbers
and the frequency checks for any duplicates

hope this helps

George


parthaemail wrote:
I like to format a column such that it allows only distinct values. for
eg in the column A i have 10 numbers 1to 10 (A1 to A10) . if i enter
any value between 1 and 10 in A11 it should not allow. A11,A12.....etc
should allow only distinct values. pls help me to do this

thanks in advance

hi all

Thanks for all you replies , It is working out.

Thank you experts


--
parthaemail
------------------------------------------------------------------------
parthaemail's Profile: http://www.excelforum.com/member.php...o&userid=31311
View this thread: http://www.excelforum.com/showthread...hreadid=519287

hi

i have a different requirement now, column a should accept only
distinct values. if same number is entered this it should give an alert
in the msg box instead of displaying in the column as duplicate. i think
we have to use macros for this. please help me

thanks in advance


--
parthaemail
------------------------------------------------------------------------
parthaemail's Profile: http://www.excelforum.com/member.php...o&userid=31311
View this thread: http://www.excelforum.com/showthread...hreadid=519287

I posted an answer a few days ago but it seems to have not worked

Try this
Goto cell A then choose 'Data Validation' from the menu

In the Allow: box choose 'custom'
In the formula: box type the following
=AND(MOD(A1,1)=0,FREQUENCY(A:A,A:A)<=1)
Enter an appropriate error message under the error alert tab
Then copy down to as many cells as needed

The mod function checks for whole numbers
and the frequency checks for any duplicates

hope this helps

George

parthaemail wrote:
hi

i have a different requirement now, column a should accept only
distinct values. if same number is entered this it should give an alert
in the msg box instead of displaying in the column as duplicate. i think
we have to use macros for this. please help me

thanks in advance

"parthaemail" wrote:
... column a should accept only distinct values.
if same number is entered this it should give an alert ..

This might work as well ..

Select col A
Data Validation
Allow: Custom
Formula: =COUNTIF(A:A,A1)<2
Click OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

hi all

The below procedure is working fine. hearty thanks for valuable
information.



Goto cell A then choose 'Data Validation' from the menu

In the Allow: box choose 'custom'
In the formula: box type the following
=AND(MOD(A1,1)=0,FREQUENCY(A:A,A:A)<=1)
Enter an appropriate error message under the error alert tab
Then copy down to as many cells as needed

The mod function checks for whole numbers
and the frequency checks for any duplicates


--
parthaemail
------------------------------------------------------------------------
parthaemail's Profile: http://www.excelforum.com/member.php...o&userid=31311
View this thread: http://www.excelforum.com/showthread...hreadid=519287