Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
Hi,
I am trying to add validation that willl do the following: 1. Prevent duplicate values being entered into a cell. 2. Only allow a 10 digit number being entered into a cell. I know how to add these methods individually, but is it possible to set both validation principles together on a cell or range of cells? I would be most grateful for any help provided. Many thanks, John. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
one way:
Select your range (assume column A, with A1 active): Choose Data/Validation: Allow: Custom Formula: =AND(ISNUMBER(A1), A1<10000000000, A1=0, INT(A1)=A1, COUNTIF(A:A,A1)<2) In article , SiH23 wrote: Hi, I am trying to add validation that willl do the following: 1. Prevent duplicate values being entered into a cell. 2. Only allow a 10 digit number being entered into a cell. I know how to add these methods individually, but is it possible to set both validation principles together on a cell or range of cells? I would be most grateful for any help provided. Many thanks, John. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
Use AND(first,second)
=AND(COUNTIF(range,first_cell)=1,AND(ISNUMBER(firs t_cell),LEN(first_cell)=10)) something like this using real cells =AND(COUNTIF($A$1:$A$10,A1)=1,AND(ISNUMBER(A1),LEN (A1)=10)) select A1:A10 with A1 as the active cell, then under allow use custom note that it (all datavalidation) can be bypassed by copying and pasting -- Regards, Peo Sjoblom "SiH23" wrote in message ... Hi, I am trying to add validation that willl do the following: 1. Prevent duplicate values being entered into a cell. 2. Only allow a 10 digit number being entered into a cell. I know how to add these methods individually, but is it possible to set both validation principles together on a cell or range of cells? I would be most grateful for any help provided. Many thanks, John. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
Assuming the formula I gave you in your previous post works, you can modify
it like this: =AND(ISNUMBER(A1),LEN(A1)=10,COUNTIF($A$1:$A$100,A 1)<=1) Again, change the cell references to meet your needs. HTH, Elkar "SiH23" wrote: Hi, I am trying to add validation that willl do the following: 1. Prevent duplicate values being entered into a cell. 2. Only allow a 10 digit number being entered into a cell. I know how to add these methods individually, but is it possible to set both validation principles together on a cell or range of cells? I would be most grateful for any help provided. Many thanks, John. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
Many, many thanks for your wonderful help. That has worked perfectly. I would
very much like to learn to write complex formulas and wondered as to whether you could advise a suitable learning resource? "Elkar" wrote: Assuming the formula I gave you in your previous post works, you can modify it like this: =AND(ISNUMBER(A1),LEN(A1)=10,COUNTIF($A$1:$A$100,A 1)<=1) Again, change the cell references to meet your needs. HTH, Elkar "SiH23" wrote: Hi, I am trying to add validation that willl do the following: 1. Prevent duplicate values being entered into a cell. 2. Only allow a 10 digit number being entered into a cell. I know how to add these methods individually, but is it possible to set both validation principles together on a cell or range of cells? I would be most grateful for any help provided. Many thanks, John. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
Glad to help!
There are a lot of good resources out there, but practice is really the best teacher. If you have the time, I'd suggest looking through these forums. Find other people's questions, and try to answer them. Then, compare your solutions to those of the experts. I think you'll find you'll pick up on a lot of neat tricks that you might not come across in a formal training. Plus, you may even help out someone else in need. HTH, Elkar "SiH23" wrote: Many, many thanks for your wonderful help. That has worked perfectly. I would very much like to learn to write complex formulas and wondered as to whether you could advise a suitable learning resource? "Elkar" wrote: Assuming the formula I gave you in your previous post works, you can modify it like this: =AND(ISNUMBER(A1),LEN(A1)=10,COUNTIF($A$1:$A$100,A 1)<=1) Again, change the cell references to meet your needs. HTH, Elkar "SiH23" wrote: Hi, I am trying to add validation that willl do the following: 1. Prevent duplicate values being entered into a cell. 2. Only allow a 10 digit number being entered into a cell. I know how to add these methods individually, but is it possible to set both validation principles together on a cell or range of cells? I would be most grateful for any help provided. Many thanks, John. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
Many thanks, I shall do that.
Just one thing I wonder whether you could help me with? I am trying to apply this formula to a a range of cells in column z with the column heading 'Cheque Number 1', but cannot get it to work. This is what I am using: =AND(ISNUMBER(X2),LEN(X2)=10,COUNTIF($X$2:$X$100,X 2)<=1) I am receiving an error message when I enter in a 10 digit number? Any ideas? I apologise if I am being a nuisance ;-) Many thanks, Simon. "Elkar" wrote: Glad to help! There are a lot of good resources out there, but practice is really the best teacher. If you have the time, I'd suggest looking through these forums. Find other people's questions, and try to answer them. Then, compare your solutions to those of the experts. I think you'll find you'll pick up on a lot of neat tricks that you might not come across in a formal training. Plus, you may even help out someone else in need. HTH, Elkar "SiH23" wrote: Many, many thanks for your wonderful help. That has worked perfectly. I would very much like to learn to write complex formulas and wondered as to whether you could advise a suitable learning resource? "Elkar" wrote: Assuming the formula I gave you in your previous post works, you can modify it like this: =AND(ISNUMBER(A1),LEN(A1)=10,COUNTIF($A$1:$A$100,A 1)<=1) Again, change the cell references to meet your needs. HTH, Elkar "SiH23" wrote: Hi, I am trying to add validation that willl do the following: 1. Prevent duplicate values being entered into a cell. 2. Only allow a 10 digit number being entered into a cell. I know how to add these methods individually, but is it possible to set both validation principles together on a cell or range of cells? I would be most grateful for any help provided. Many thanks, John. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
Refresh existing data when changed in data validation list | New Users to Excel |