Formula to create surrogate key?

[scottbass]

Hi,

Sorry if this has been asked before...I Googled before posting but didn't quite know the search string to use, so got a lot of false hits.

Is it possible to use a formula 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):

VAR  KEY
A     1     : Add "A", key becomes 1
B     2     : Add "B", key becomes 2

Delete row A

VAR  KEY
B     2
C     3     : Add "C", key becomes 3

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 often opens 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]

Hi,

Any comments on this approach below) (pseudocode)

Workbook_Open Event:
Calculate MAX(key) and store in a "scratchpad" somewhere (i.e. hidden worksheet, hidden column in current worksheet, etc.). Let's call this cell MAX_KEY.

Worksheet_Change Event:
(Is there a way to detect a "new" row???)
Otherwise, If Key Is Missing for updated row:
Pull current value of MAX_KEY into VBA variable.
Increment VBA variable.
Store VBA variable back to MAX_KEY.
Store VBA variable in Key column of updated row.

Bad things can happen if the end user modifies this Key value, as it is used to "join" (INDEX MATCH) rows in other worksheets. So once a Key has been derived for a row, it needs to remain unchanged for that row. So, is it possible to protect the Key column, yet have VBA update it as well?

Finally, the Workbook will be shared. It's possible that end users could create new rows concurrently. Would the above approach ensure that each row has a unique Key when it is initially added?

Thanks,
Scott

 

[scottbass]

Hi,

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

Sheet1:

KEY	VALUE
1
2
3
4
5
6
7
8
9
10

<tbody>
</tbody>

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

Sheet2:

MAX_KEY

[value set by Workbook_Open Event]<set by="" workbook_open="" event=""></set>

<tbody>
</tbody>

Cell $A$2 is protected (MAX_KEY value)

I've created the named ranges:

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

VBA Code:

ThisWorkbook (yeah, I'll add a password later, I'm still developing...):

Private Sub Workbook_Open()
    ' Protect worksheets for User Interface Only
    Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        wSheet.Protect , UserInterFaceOnly:=True
    Next wSheet
    
    ' Store value of max key in MAX_KEY
    Range("MAX_KEY").Value = Application.Max(Range("Key"))
End Sub

Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    'On Error GoTo Whoops
    Application.EnableEvents = False
    
    ' Get Range for MAX_KEY
    Dim rngMAX_KEY As Range
    Set rngMAX_KEY = ActiveWorkbook.Names("MAX_KEY").RefersToRange
    
    ' Get row that was changed
    iRow = Target.Row
    
    ' Get value of Key for this row
    iKey = Range("A" & iRow).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 WorksheetFunction.CountA(Target.EntireRow) = 0 Then GoTo Whoops
        
        iMaxKey = rngMAX_KEY.Value
        iMaxKey = iMaxKey + 1
        rngMAX_KEY.Value = iMaxKey
        Range("A" & iRow).Value = iMaxKey
    End If
    
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: Is there a way I can use the "Key" named range to get the value of Key for the modified row? IOW, I want "get me the value of the Key cell where the column is defined by the Key range, and the row is defined by the Target.Row". Perhaps using the Intersect of Key (a column) * Target.EntireRow (a row)? If I can't do this, I'll just run with the hardcoded column; it will always be in column A.

3) 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???

BTW, Excel 2007 on Win7 64 bit.

Thanks,
Scott

 

[scottbass]

One more minor point: if I paste a block of text at the bottom, say copy and paste the last three values to new rows, only the first new row gets a derived key.

 

[scottbass]

Apologies, final code update of the day!

ThisWorkbook:

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

Again, this all fails once I turn on Workbook Sharing...