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):

Please Login or Register  to view this content.

Sheet1:

Please Login or Register  to view this content.

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