Create Guid's Using a Formula??

[gonsalvr]

I have a spreadsheet that identifies my patients for internal purposes using personal information, but if I do a request for an x-ray or MRI I can't use Private Health Information to track. I would like to create a Unique number, that does not recalculate everytime you click on another cell in the sheet. I have been researching on the Web for days and I keep ending up back at VBA. I'm a complete Noob regarding that topic.

 

[SirJB7]

Hi, gonsalvr!

If you're not going to sort/insert-in-the-middle/delete-in-the-middle rows in the sheet that contains the patient data, you can add a column and type on first data cell "=ROW()-1" unquoted (if headers are more than 1 row height change the "-1" to proper value; if you want to start from 1000 value, add "+1000" to the formula.

If you're going to perform such operations, well you must have a table with the last number used, and use a button control or an event handler to perform the allocation of a new number whenever you add a patient. But that's much more complex than previous.

Regards!

 

[gonsalvr]

SirJB7, Thank you. From my research, it was looking like I will have to take a crash course in VBA and learn how to insert code into an Excel module. This database is an ongoing live tracking tool of care. So yes as we add or remove patients we do insert rows and delete rows etc., and that causes the GUID to recalculate and I need static numbers.

 

[NARAYANK991]

Hi ,


Is this worksheet / database to be used only by you ? Or will it be used by several people ?


If it is only under your custody , why don't you create a database of GUIDs on a separate worksheet , by using the RANDBETWEEN function to generate the random numbers , as many as you want. Giving a start number of 100000 and an end number of 999999 , and copying it down as many rows as you want , will generate enough random numbers. Now do a copy + Paste Special -> Values. Since there will be duplicates in this list , use the Excel feature of Remove Duplicates , and you have a static list of random numbers , without duplicates.


Now it is a matter of assigning a new random number from this list , each time you want one ; can you do it based on some key , such as their Social Security Number ?


The point here is that the list of random numbers is in a separate sheet , which will not be affected by any operation in your normal worksheets.


Narayan

 

[SirJB7]

Hi, gonsalvr!


You can try this, if it's suitable for you.


Let's say your new public PatientID is in column A (if not, change to proper value in following code where it says 'kiIDColumn = ...'), and that it has a formula like "=ROW()-X+Y" unquoted, where X is the number of header lines and Y+1 the number from which you want to start automatic numeration.


Let's say your worksheet name is "Hoja1" unquoted (if not, change to proper value in following code where it says 'ksSheet = ...').


Insert a new module (Alt-F11, Insert, Module) and copy this code in the upper right pane:

-----

[pre]

Option Explicit

Sub TransformFormulaIntoValue()
' constants
Const ksSheet = "Hoja1"
Const kiIDColumn = 1
' declarations
Dim I As Long, J As Long, K As Long
' start
If ActiveSheet.Name <> ksSheet Then Exit Sub
' process
With Selection
For I = 1 To .Rows.Count
J = I + .Row - 1
K = Cells(J, kiIDColumn).Value
If K > 0 Then
If Cells(J, kiIDColumn).HasFormula Then Cells(J, kiIDColumn).Value = K
End If
Next I
End With
' end
End Sub

[/pre]
-----


What does this code do? Nothing until you...:

a) press Alt-F8 from within sheet "Hoja1" and choose run "TransformFormulaIntoValue" macro

b) assign it to a button control (or command button) and click it


What does this code do when you execute it?

It operates on column kiIdColumn of sheet ksSheet and only on cells corresponding to selected cells (one or multiple cells), and it changes the formula by its value, fixing it and keeping it constant for future uses.


How/when should you use this procedure?

First, if you have 100 patients copy the formula for column A to 120 rows, select the first 100 and execute the macro: this will leave all actual patients with a constant invariable ID and reserve place for future additions.

Second, each time you add a new patient, select any cell of its row, and execute the macro: this will change its formula value into constant.


Hope it helps you. Just advise if any trouble.


Regards!