Preventing duplicates within two columns

J

jacey2007

Hi

I have been asked by a work colleague to design a spreadsheet for the issue
of production logbooks. We use excel 2002 at work and the site policy is not
to use VB within excel. The logbooks are issued by number and the initial
version is set to version 1. For example a logbook will be issued as LB1234
Version 1. on completion of Version 1, LB1234 Version 2 is issued. So, in my
spreadsheet Column A Header is Logbook Number and Column B is Version Number,
there are another 15 or so columns of information with these columns. My
colleague wants to be able to enter the next available Version Number against
a particular Logbook Number. I have argued that it would be easier to use
autofilters and the sort the results with Version Number but I'm wondering if
there is a way of using the Validation function. There are already 3600
records and the average input is about 1000 rows of information per annum. I
know it's screaming out for a database but my superiors insist we use
spreadsheets as it's difficult to get databases validated. Can anyone help me
please?
 
K

Ken Johnson

Hi

I have been asked by a work colleague to design a spreadsheet for the issue
of production logbooks. We use excel 2002 at work and the site policy is not
to use VB within excel. The logbooks are issued by number and the initial
version is set to version 1. For example a logbook will be issued as LB1234
Version 1. on completion of Version 1, LB1234 Version 2 is issued. So, in my
spreadsheet Column A Header is Logbook Number and Column B is Version Number,
there are another 15 or so columns of information with these columns. My
colleague wants to be able to enter the next available Version Number against
a particular Logbook Number. I have argued that it would be easier to use
autofilters and the sort the results with Version Number but I'm wondering if
there is a way of using the Validation function. There are already 3600
records and the average input is about 1000 rows of information per annum. I
know it's screaming out for a database but my superiors insist we use
spreadsheets as it's difficult to get databases validated. Can anyone help me
please?
Click to expand...

Maybe Custom validation...

After selecting from B2 down to the bottom of the data use this
formula in the custom Formula: box

=COUNTIF(A$2:A2,A2)=B2

Ken Johnson
 
J

jacey2007

Many thanks Ken,

John (jacey2006)

Ken Johnson said:
Maybe Custom validation...

After selecting from B2 down to the bottom of the data use this
formula in the custom Formula: box

=COUNTIF(A$2:A2,A2)=B2

Ken Johnson
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell values not being properly evaluated by VLOOKUP() orSUMPRODUCT()... why? 1
Matching up two columns with different data 1
If Name is checked on Sheet 1, mark appropriate column in Sheet 2?? 2
Pivot Table from linked spreadsheets 1
Month lookup with multiple years 9
Need to Sum Non Numerical Columns and Create a Chart in Office 2003 Excel/Power Point 5
Excel 2007 Find and Replace Scroll Issues 1
merging two large spreadsheets 2

Top