Formula to create surrogate key?

[scottbass]

Is it possible to use a formula or VBA to create a "surrogate key" column in Excel? This key would simply be an integer that would be "max of current column + 1".

Example (starting with an empty worksheet):

KEY	VALUE	COMMENT
1	A	Add "A", key becomes 1
2	B	Add "B", key becomes 2

For now, I can live with the end user deleting a row, and live with the risk that they delete the max key. I may turn on workbook protection to prevent that, although that may open a can of worms.

Alternatively, perhaps I copy the max value to a cell somewhere, and always maintain the max key separate from the column itself??? I assume this approach requires VBA, called say from the worksheet_change event???

Hopefully this is clear. Essentially, when the end user adds a new row, I want a new CaseId to be assigned to the new row, but only when a CaseId has not already been assigned.

Thanks,
Scott

[scottbass]

I've made progress on this, here is what I have so far:

Sheet1:

KEY	VALUE
1	A
2	B
3	C
4	D
5	E

I've unprotected the cells in "Value". The cells in "Key" (i.e. column A) are protected.

Sheet2:

MAXKEY

[value set by Workbook_Open Event]

Cell $A$2 is protected (MAXKEY value)

I've created the named ranges:

Key: =Sheet1!$A:$A
MAX_KEY: =Sheet2!$A$2

VBA Code:

ThisWorkbook (I'll add a password later):

Private Sub Workbook_Open()
    ' Protect worksheets for User Interface Only
    Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        wSheet.Protect _
        Password:="", _
        AllowSorting:=True, _
        AllowFiltering:=True, _
        UserInterFaceOnly:=True
    Next wSheet
    
    ' Store value of max key in MAX_KEY
    ActiveWorkbook.Names("MAX_KEY").RefersToRange.Cells(1, 1) = Application.Max(Range("Keys"))
End Sub

Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoops
    Application.EnableEvents = False
    
    Dim rngMaxKey, rngKeys As Range
    
    ' Get the range for MAX_KEY
    Set rngMaxKey = ActiveWorkbook.Names("MAX_KEY").RefersToRange
    
    ' Get the range for Keys
    Set rngKeys = ActiveWorkbook.Names("Keys").RefersToRange
    
    ' Process each row in Target (multiple rows in Target if copy and paste)
    For Each rngRow In Target.EntireRow
    
        ' Get value of Key for this row
        iKey = Intersect(rngKeys, rngRow).Value
    
        ' If the Key is missing then increment
        If IsEmpty(iKey) Then
            ' But is the entire row empty, such as a delete or clear?
            ' If so we don't want to increment the key
            ' Note: This won't work if the row contains formulas
            If WorksheetFunction.CountA(Target.EntireRow) = 0 Then GoTo Whoops
            
            iMaxKey = rngMaxKey.Cells(1, 1) ' MAX_KEY range should be a single cell anyway
            iMaxKey = iMaxKey + 1
            rngMaxKey.Cells(1, 1).Value = iMaxKey
            Intersect(rngKeys, rngRow).Value = iMaxKey
        End If
    Next
    
Whoops:
   Application.EnableEvents = True
End Sub

I also turned on Allow User To Edit Ranges and set other permissions that allows the end user to Filter or Sort the data in Sheet1, i.e. by the VALUE column.

This worked pretty well. If I add text to "Value", it derives a new Key. I can't fiddle with the value of Key once it's derived, since the column is protected. I can't delete a row once it's created (which is what I want). Ideally once Value is entered, it becomes "bound" to the key, i.e. cannot be changed further. Perhaps I can do this via VBA to protect the cell once it's been set.

HOWEVER, I have a few issues:

1) MAJOR: But this all falls apart when I turn on workbook sharing. The end users MUST be able to do concurrent edits on the workbook. I'm dumbfounded why Microsoft made UserInterfaceOnly a run time property, rather than a "checkbox" property along with the other configurable protection properties??? I've seen many other hits where end users complained about this.

2) Minor: The code works (ignoring protection), but suggestions to clean it up are welcome. I should probably declare variable types, etc.

Any ideas, esp. re: #1??? I may have to change my approach to having one workbook per end user, then consolidate the data via a 3rd party application (SAS) and write the consolidated data to another "reporting" workbook. If I do this, I would like MAXKEY maintained on a 2nd, shared workbook, so that whenever any user adds a new row, they get the next sequential key. But, I worry if two (or more) end users create a new row simultaneously. Finally, I'd like to maintain all the data validation lookups on this centralised, shared 2nd workbook as well.

Thanks,
Scott