safety checks?

[lil'bit]

Hi. I am working in excel 2003 and have monthly spreadsheets with
invoices listed. Sometimes an invoice *#* will be repeated on the
following month's sheets. I would like to set up excel to show a
warning?, print it in red?, or somehow show that it's a duplicate #. Is
there a way to do this? It would involve excel being aware of all
invoice #'s on all the spreadsheets. This might be in the realm of
fanatasy but thought I'd take a shot. Thank you.

 

[Andrew]

Try this site.

http://www.beyondtechnology.com/tips020.shtml

You don't have to use Data Validation, you can also use Conditional
Formatting.

I use a slightly more complex system at work - different customers,
same day shipments etc. so I use a combination of IF, COUNTIF and
Conditional Formatting.

 

[Norman Harker]

Hi lil'bit!

See:

Chip Pearson:
http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

Chip provides a conditional formatting formula for highlighting
duplicates.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[lil'bit]

Thank you Andrew. That is an awesome site. I bookmarked it for futur
reference. So easy to understand for the neophyte's like me. Reall
liked it.

And Norman, as usual, you are the wonderkind kid. Thank you.

However, neither site helped for the particular problem, (maybe). I a
totally new at this and thus was not clear with what was needed or
simply didn't understand Mr. Pearson's instructions. I'm sorry. ( I'
sorta like the sheriff in "Jaws" that knows nothing about and hates th
water but is rushing out in the boat to save the kids. The deputy i
giving him nautical directions and the sheriff says "just point".
That's me! A point and click kind of person. So, anyway,I'll tr
this again.

I have several -different- spreadsheets for each month which have inv
numbers listed. I don't want to have to check the preceding month'
sheets for an inv. # that is already listed when I am doing the curren
month's listings. Is there way to have excel highlite an invoice # tha
has already been listed in a preceding month? er...uh..Well I did sa
it might be in the realm of fanatasy. Or, at least my uninspire
fanatasies. ;

 

[Norman Harker]

Hi lil'bit!

First name the previous month's invoice block MyPrevRange
Select this month's block of cells
Format > Conditional formatting
Formula Is:
=COUNTIF(MyPrevRange,A6)>0
Press "Format" button
Select Highlighting format
OK
OK
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[lil'bit]

Argh! Curses, foiled again. Hi Norman. It didn't work. Gulp! I feel
just a tad.................wait for it...............................
stupid.
Here's what I did:

"First name the previous month's invoice block MyPrevRange"

named entire sheet of previous month MyPrevRange ( also tried just the
cells with inv #'s)

"Select this month's block of cells"

highlited all the columns and rows (also tried w/o highlites, and also
with just the invoice cells with and w/o highlites)
Entered an Invoice # that was the same as last month's.

"Format > Conditional formatting"

Did that. Selected "formula is".
Entered "=COUNTIF(MyPrevRange,A6)>0"
Clicked format button
Selected Italics and color
clicked "OK" twice


The inv # that I entered on current worksheet does not appear any
different. What did I do wrong?

 

[Norman Harker]

Hi lil'bit!

I've sent you a very simple workbook with it working.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Andrew]

How does your invoicing system work?

For example, my company starts off with Invoice No. 001/04 for each
customer so one customer might work their way up to 054/04 whereas
another customer might work they way up to 003/04.

Mr. Harker, can I get a copy of that workbook too? Just to continue my
education process.

 

[Norman Harker]

Hi Andrew!

By all means send a request direct by e-mail. But in this case I was
merely implementing the solution I gave in the previous post. lil'bit
had had problems with doing it and I thought that a working version of
conditional formatting that checks entries on a previous sheet might
help more than going through it again.

Sometimes posters think that if it doesn't work, then the solution
must be wrong. I try to test all of mine before sending but.... It's
called Murphy's Law.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[lil'bit]

"Sometimes posters think that if it doesn't work, then the solution
must be wrong."
Heavens no! I practically think you walk on water.

However, Just highlite me blue because, my fault , I can't get it t
work even with the worksheet example. sigh....

My brain must be excel logged. Going to bed and sulk

 

[Norman Harker]

Hi lil'bit!

After your sulk, you can always send the file direct and I'll see if I
can sort it.

'fraid that I only walk on frozen water and there's not much around
here with temps +30C.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.