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