Hi Everyone,
I am very new to Excel and I need to enter a series of figures into a range
of cells, say 1 to 400. How do I ensure that:

a) only figures are accepted, and must be between 1 & 400
b) the figures are not duplicated?

Thanks for your assistance.
--
Glint

Use Data Validation.

Select all your cells, let's assume it is H1:Hn

DataValidation, change allow type to Custom and add a formula of

=AND(H11,H1<=400,COUNTIF($H$1:H1,H1)=1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Glint" wrote in message
...
Hi Everyone,
I am very new to Excel and I need to enter a series of figures into a
range
of cells, say 1 to 400. How do I ensure that:

a) only figures are accepted, and must be between 1 & 400
b) the figures are not duplicated?

Thanks for your assistance.
--
Glint

Thanx Bob, but can you spoon-feed me a little? It is so bad I even have
difficulty entring the formula.
The range is B2:Z26. I need to enter 1 to 225 without duplicating any
number. Nulls are allowed. How do I do it?
--
Glint


"Bob Phillips" wrote:

Use Data Validation.

Select all your cells, let's assume it is H1:Hn

DataValidation, change allow type to Custom and add a formula of

=AND(H11,H1<=400,COUNTIF($H$1:H1,H1)=1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Glint" wrote in message
...
Hi Everyone,
I am very new to Excel and I need to enter a series of figures into a
range
of cells, say 1 to 400. How do I ensure that:

a) only figures are accepted, and must be between 1 & 400
b) the figures are not duplicated?

Thanks for your assistance.
--
Glint

Hi Glint,
Looks like you've changed from 400 to 225, so the formula should be...

=AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1)

If so, try these steps to get it in place and working...

1. Select B2:Z26
2. Go Data|Validation...
3. On the Settings Tab of the Data Validation dialog, click in the
Allow: box and select Custom.
4. Type the above formula into the Formula: box
5. If you want a message to appear when a cell in the range is selected
add it to the Input Message Tab of the Data Validation dialog
6. If you want a warning Error Alert message displayed when the user
tries to enter an invalid value then add it to the Error Alert tab of
the Data Validation dialog.
7. Click OK.

Ken Johnson

Thanx Ken. I did not realize at first that the formula only works in a blank
worksheet. I had problems with one that had data.
--
Glint


"Ken Johnson" wrote:

Hi Glint,
Looks like you've changed from 400 to 225, so the formula should be...

=AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1)

If so, try these steps to get it in place and working...

1. Select B2:Z26
2. Go Data|Validation...
3. On the Settings Tab of the Data Validation dialog, click in the
Allow: box and select Custom.
4. Type the above formula into the Formula: box
5. If you want a message to appear when a cell in the range is selected
add it to the Input Message Tab of the Data Validation dialog
6. If you want a warning Error Alert message displayed when the user
tries to enter an invalid value then add it to the Error Alert tab of
the Data Validation dialog.
7. Click OK.

Ken Johnson

What do you mean that it only works in a blank worksheet? There is no point
if the workbook is blank.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Glint" wrote in message
...
Thanx Ken. I did not realize at first that the formula only works in a
blank
worksheet. I had problems with one that had data.
--
Glint


"Ken Johnson" wrote:

Hi Glint,
Looks like you've changed from 400 to 225, so the formula should be...

=AND(B2=1,B2<=225,COUNTIF($B$2:Z26,B2)=1)

If so, try these steps to get it in place and working...

1. Select B2:Z26
2. Go Data|Validation...
3. On the Settings Tab of the Data Validation dialog, click in the
Allow: box and select Custom.
4. Type the above formula into the Formula: box
5. If you want a message to appear when a cell in the range is selected
add it to the Input Message Tab of the Data Validation dialog
6. If you want a warning Error Alert message displayed when the user
tries to enter an invalid value then add it to the Error Alert tab of
the Data Validation dialog.
7. Click OK.

Ken Johnson