How can I define my own Primary Key in Excel?
 

I know that Excel uses the cell reference as a unique identifier, but can I
define my own "Primary Key" to ensure the uniqueness of a particular field
(for example, National Insurance Number)? I know that I could do this easily
in Access, but the rest of my task is so simple, using Access seems rather
like using a sledgehammer to crack a nut!

Many thanks

How can I define my own Primary Key in Excel?
 

You can name the cell:

Select the cell and on the Excel menu bar - InsertNaneDefine then enter
the name you want to use and click AddOK. You can now refer to that name
in formulas and in code to identify that specific cell. See Excel help
files for details of using named ranges.

In VBA you can Use an Object Variable, to do the same thing.

Set myRange = ActiveSheet.Range("A1")

This will allow you to use myRange in code any time you want to refer to
Range("A1").
See VBA help files for details of using Object Variables.


"auntiechrissie" wrote in message
...
I know that Excel uses the cell reference as a unique identifier, but can I
define my own "Primary Key" to ensure the uniqueness of a particular field
(for example, National Insurance Number)? I know that I could do this
easily
in Access, but the rest of my task is so simple, using Access seems rather
like using a sledgehammer to crack a nut!

Many thanks

How can I define my own Primary Key in Excel?
 

auntiechrissie;570084 Wrote:
I know that Excel uses the cell reference as a unique identifier, but
can I
define my own "Primary Key" to ensure the uniqueness of a particular
field
(for example, National Insurance Number)? I know that I could do this
easily
in Access, but the rest of my task is so simple, using Access seems
rather
like using a sledgehammer to crack a nut!

Many thanks
You can do it in a number of ways, here's one:
Do this before entering data, not afterwards.

- First select all the cells in the column which you want to be
unique.
- Go to Data Validation (DV) and choose Custom in the Allow: field
- In the Formula field enter the likes of:
=COUNTIF($A:$A,$A20)<2
but be aware of the following:

- In the case above I had selected the range A20:A40 to put the DV
in and A20 was the active cell, hence the $A20 part of the formula.
This doesn't mean the whole selection's DV will look at A20 by the
way.
- I chose to get DV to check against the whole of column A, hence
the $A:$A part of the formula. This could be a smaller range.

- Click OK


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157497

Microsoft Office Help

How can I define my own Primary Key in Excel?
 

You can simulate the "autonumber" feature from Access with a couple of
methods proposed by J.E. McGimpsey that are documented he

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

hopefully that'll help you with the issue.

"auntiechrissie" wrote:

I know that Excel uses the cell reference as a unique identifier, but can I
define my own "Primary Key" to ensure the uniqueness of a particular field
(for example, National Insurance Number)? I know that I could do this easily
in Access, but the rest of my task is so simple, using Access seems rather
like using a sledgehammer to crack a nut!

Many thanks

How can I define my own Primary Key in Excel?
 

On second thought, it might not be so complex as to require a McGimpsey
solution. Let's say that your key column will be column C and that C1 has a
label (as "National Insurance Number") in it and your data entries start on
row 2, then in cell C2 put this formula:
=MAX(C$1:C1)+1
which should return 1. Fill that formula on down the sheet and the number
will auto increment.

If you need to start with a 'seed' value, then make C2's formula something
like
=MAX(C$1:C1)+1+9944
where 9944 is your 'seed' value. Then in C3 you put the formula:
=MAX(C$1:C2)+1
and fill that on down the sheet.

You can modify that formula to add other things to the number, such as text,
or to format the result to a specific # of digits, as (in C2)
=TEXT(ROW()-ROW(C$1)+9944,"000000")
to get 6-digits displayed and fill that formula on down the sheet to
increment the value displayed.

or get really creative with something like this in C2
="NINABC-22-" & TEXT(ROW()-ROW(C$1)+9944,"000000")
which will display NINABC-22-009945 in the cell.

--Trying to post for 2nd time.

"auntiechrissie" wrote:

I know that Excel uses the cell reference as a unique identifier, but can I
define my own "Primary Key" to ensure the uniqueness of a particular field
(for example, National Insurance Number)? I know that I could do this easily
in Access, but the rest of my task is so simple, using Access seems rather
like using a sledgehammer to crack a nut!

Many thanks

How can I define my own Primary Key in Excel?
 

In the VBA help files under both Primary Property and Unique Property, there
are code samples along with descriptive narrative that might be what you are
looking for. It apparently involves DAO and tables, but it sounds like what
you want.


"auntiechrissie" wrote in message
...
I know that Excel uses the cell reference as a unique identifier, but can I
define my own "Primary Key" to ensure the uniqueness of a particular field
(for example, National Insurance Number)? I know that I could do this
easily
in Access, but the rest of my task is so simple, using Access seems rather
like using a sledgehammer to crack a nut!

Many thanks