How can I define my own Primary Key in Excel?

[auntiechrissie]

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

 

[JLGWhiz]

You can name the cell:

Select the cell and on the Excel menu bar - Insert>Nane>Define then enter
the name you want to use and click Add>OK. 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.

 

[p45cal]

I know that Excel uses the cell reference as a unique identifier, bu
can I
define my own "Primary Key" to ensure the uniqueness of a particula
field
(for example, National Insurance Number)? I know that I could do thi
easily
in Access, but the rest of my task is so simple, using Access seem
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 b
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 D
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 th
way.
- I chose to get DV to check against the whole of column A, henc
the $A:$A part of the formula. This could be a smaller range.

- Click O

 

[JLatham]

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

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

hopefully that'll help you with the issue.

 

[JLatham]

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.

 

[JLGWhiz]

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.