I am trying to prepare a new sales order management spreadsheet for my
Warehouse staff to record orders reaching them for picking and packing.
Nothing fancy but I have a definiative requirement in stopping orders of a
five digit format from being entered twice unless it is a back-fill order
where we can have a forward slash with a number or letter reference added too.

John,

Try Conditional Formatting using a formula.. You can use it to highlight
duplicates in (say) Column A with the formula:

=COUNTIF($A:$A,$A1)1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
I am trying to prepare a new sales order management spreadsheet for my
Warehouse staff to record orders reaching them for picking and packing.
Nothing fancy but I have a definiative requirement in stopping orders of a
five digit format from being entered twice unless it is a back-fill order
where we can have a forward slash with a number or letter reference added
too.

Hi Sandy

Thanks for the response. Sadly, Im not as good as I thought and can't make
this formula work in conditioning formatting? I'm using column D in the
spreadsheet where I am trying to prevent two of the same number being
entered. Actual cells are D4 to D250 after column headers etc. Currently,
others headers turn red and bold as soon as any entry is made in column D4
downwards, the conditioning formatting applies but does not indicate a
dublicate entry??

Any further thoughts please???

"Sandy Mann" wrote:

John,

Try Conditional Formatting using a formula.. You can use it to highlight
duplicates in (say) Column A with the formula:

=COUNTIF($A:$A,$A1)1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
I am trying to prepare a new sales order management spreadsheet for my
Warehouse staff to record orders reaching them for picking and packing.
Nothing fancy but I have a definiative requirement in stopping orders of a
five digit format from being entered twice unless it is a back-fill order
where we can have a forward slash with a number or letter reference added
too.

Hi John,

Follow me through:

Select D4:D250
With the range still selected, select Format Conditional Formatting
Formula Is
then type in:
=COUNTIF($D$4:$D$250,$D4)1
then select the highlighting colour that you want.

I then only get highlighted cell when I have duplictes.

Post back if you still can't get it to work.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
Hi Sandy

Thanks for the response. Sadly, Im not as good as I thought and can't
make
this formula work in conditioning formatting? I'm using column D in the
spreadsheet where I am trying to prevent two of the same number being
entered. Actual cells are D4 to D250 after column headers etc.
Currently,
others headers turn red and bold as soon as any entry is made in column D4
downwards, the conditioning formatting applies but does not indicate a
dublicate entry??

Any further thoughts please???

"Sandy Mann" wrote:

John,

Try Conditional Formatting using a formula.. You can use it to highlight
duplicates in (say) Column A with the formula:

=COUNTIF($A:$A,$A1)1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
I am trying to prepare a new sales order management spreadsheet for my
Warehouse staff to record orders reaching them for picking and packing.
Nothing fancy but I have a definiative requirement in stopping orders
of a
five digit format from being entered twice unless it is a back-fill
order
where we can have a forward slash with a number or letter reference
added
too.

Will do Sandy, I am sitting here now as I need to complete this for Tuesday
morning but dont want to commit the whole bank holiday weekend to it! If I
can resolve this now so much the better as we're off out later this evening
too!

Thanks

Rgds John

"Sandy Mann" wrote:

Hi John,

Follow me through:

Select D4:D250
With the range still selected, select Format Conditional Formatting
Formula Is
then type in:
=COUNTIF($D$4:$D$250,$D4)1
then select the highlighting colour that you want.

I then only get highlighted cell when I have duplictes.

Post back if you still can't get it to work.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
Hi Sandy

Thanks for the response. Sadly, Im not as good as I thought and can't
make
this formula work in conditioning formatting? I'm using column D in the
spreadsheet where I am trying to prevent two of the same number being
entered. Actual cells are D4 to D250 after column headers etc.
Currently,
others headers turn red and bold as soon as any entry is made in column D4
downwards, the conditioning formatting applies but does not indicate a
dublicate entry??

Any further thoughts please???

"Sandy Mann" wrote:

John,

Try Conditional Formatting using a formula.. You can use it to highlight
duplicates in (say) Column A with the formula:

=COUNTIF($A:$A,$A1)1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
I am trying to prepare a new sales order management spreadsheet for my
Warehouse staff to record orders reaching them for picking and packing.
Nothing fancy but I have a definiative requirement in stopping orders
of a
five digit format from being entered twice unless it is a back-fill
order
where we can have a forward slash with a number or letter reference
added
too.

OK! cracked it.

I have it turning red and everything on a double entry. Thank you. Unable
to make an error warning to appear though just the cells turning red.

Any thoughts??

John

"Sandy Mann" wrote:

Hi John,

Follow me through:

Select D4:D250
With the range still selected, select Format Conditional Formatting
Formula Is
then type in:
=COUNTIF($D$4:$D$250,$D4)1
then select the highlighting colour that you want.

I then only get highlighted cell when I have duplictes.

Post back if you still can't get it to work.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
Hi Sandy

Thanks for the response. Sadly, Im not as good as I thought and can't
make
this formula work in conditioning formatting? I'm using column D in the
spreadsheet where I am trying to prevent two of the same number being
entered. Actual cells are D4 to D250 after column headers etc.
Currently,
others headers turn red and bold as soon as any entry is made in column D4
downwards, the conditioning formatting applies but does not indicate a
dublicate entry??

Any further thoughts please???

"Sandy Mann" wrote:

John,

Try Conditional Formatting using a formula.. You can use it to highlight
duplicates in (say) Column A with the formula:

=COUNTIF($A:$A,$A1)1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
I am trying to prepare a new sales order management spreadsheet for my
Warehouse staff to record orders reaching them for picking and packing.
Nothing fancy but I have a definiative requirement in stopping orders
of a
five digit format from being entered twice unless it is a back-fill
order
where we can have a forward slash with a number or letter reference
added
too.

John

If you want a warning or restriction, you must use Data Validation.

See Chip Pearson's site for preventing duplicates.

http://www.cpearson.com/excel/NoDupEntry.htm


Gord Dibben MS Excel MVP

On Sat, 5 May 2007 09:27:00 -0700, John wrote:

OK! cracked it.

I have it turning red and everything on a double entry. Thank you. Unable
to make an error warning to appear though just the cells turning red.

Any thoughts??

John

"Sandy Mann" wrote:

Hi John,

Follow me through:

Select D4:D250
With the range still selected, select Format Conditional Formatting
Formula Is
then type in:
=COUNTIF($D$4:$D$250,$D4)1
then select the highlighting colour that you want.

I then only get highlighted cell when I have duplictes.

Post back if you still can't get it to work.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
Hi Sandy

Thanks for the response. Sadly, Im not as good as I thought and can't
make
this formula work in conditioning formatting? I'm using column D in the
spreadsheet where I am trying to prevent two of the same number being
entered. Actual cells are D4 to D250 after column headers etc.
Currently,
others headers turn red and bold as soon as any entry is made in column D4
downwards, the conditioning formatting applies but does not indicate a
dublicate entry??

Any further thoughts please???

"Sandy Mann" wrote:

John,

Try Conditional Formatting using a formula.. You can use it to highlight
duplicates in (say) Column A with the formula:

=COUNTIF($A:$A,$A1)1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
I am trying to prepare a new sales order management spreadsheet for my
Warehouse staff to record orders reaching them for picking and packing.
Nothing fancy but I have a definiative requirement in stopping orders
of a
five digit format from being entered twice unless it is a back-fill
order
where we can have a forward slash with a number or letter reference
added
too.

For an error message you could use Data Validation as well:

Select the range as before and then:

Data Validation Settings Allow = Custom Formula
=SUMPRODUCT(--(EXACT(D4,$D$4:$D$250)))=1

then Error Alert and enter the message you want.

The error message will appear and the other duplicate will be highlighted

The EXACT function is needed because Validation found part numbers or other
Order numbers.


An alternative is to enter in E4:

=IF(COUNTIF($D$4:$D$250,$D$4:$D$250)1,"Double Order Number","")

and copy down to E250

Or to have only one cell giving the error message leave it in E4


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
OK! cracked it.

I have it turning red and everything on a double entry. Thank you. Unable
to make an error warning to appear though just the cells turning red.

Any thoughts??

John

"Sandy Mann" wrote:

Hi John,

Follow me through:

Select D4:D250
With the range still selected, select Format Conditional Formatting
Formula Is
then type in:
=COUNTIF($D$4:$D$250,$D4)1
then select the highlighting colour that you want.

I then only get highlighted cell when I have duplictes.

Post back if you still can't get it to work.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
Hi Sandy

Thanks for the response. Sadly, Im not as good as I thought and can't
make
this formula work in conditioning formatting? I'm using column D in
the
spreadsheet where I am trying to prevent two of the same number being
entered. Actual cells are D4 to D250 after column headers etc.
Currently,
others headers turn red and bold as soon as any entry is made in column
D4
downwards, the conditioning formatting applies but does not indicate a
dublicate entry??

Any further thoughts please???

"Sandy Mann" wrote:

John,

Try Conditional Formatting using a formula.. You can use it to
highlight
duplicates in (say) Column A with the formula:

=COUNTIF($A:$A,$A1)1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"John" wrote in message
...
I am trying to prepare a new sales order management spreadsheet for
my
Warehouse staff to record orders reaching them for picking and
packing.
Nothing fancy but I have a definiative requirement in stopping
orders
of a
five digit format from being entered twice unless it is a back-fill
order
where we can have a forward slash with a number or letter reference
added
too.