Hospital Patient Worksheet Project

[hortoncj]

Hello, all! First time poster looking for some advice. I'm a respiratory therapist working in a large county hospital. Until recently, we were using a complicated system to organize our patient load involving cutting up patient stickers and applying them to long sheets of grid-lined paper. We now have decided to use Excel to organize our patients. We have one Excel document with a different worksheet for each area of the hospital (ICU, ER, 3D, 3B, etc.)

Currently, I have the worksheet protected so that only the DATE and UNIT fields, the room # fields, and the fields with all the patient information (including the cell with "MR#") are unlocked so people can edit the information without affecting the formatting. Currently our employees have to copy/paste the information for each patient to arrange them in order by room number when someone is moved, discharged, or someone new is admitted. This is proving to be complicated for some people who aren't that experienced with Excel (or computers in general) and is resulting in some errors.

All the cells on the right half of the document with the various numbers are for billing purposes and should stay the same at all times.

I really have two questions: One, is it possible to "group" the information per patient somehow, then "Sort" the information by room number so that all the patients are in order, with the correct information, without having to copy/paste? And two: Is there a way to make this process automatic, so the program will automatically arrange the patients in order of room # when one is deleted, or once all the information for a new one has been added?

Thanks so much for your help.

 

[Taul]

Hi,
Welcome to the board.
Yes it is possible to group the data ready for sorting, check out the “Group” command in the excel help file. It is difficult to explain but the help file lays it out quite neatly.
As for automation, most of the issues will be related to the way the data is organised. Would it be possible to layout the data in a different way, such that each patient‘s details are in a unique row? This would make it easier to automate the sort. You implied the billing data on the right should remain as it is currently laid out, are these ‘tablets of stone’? Or can that portion be adapted?

cheers
Paul H

 

[hortoncj]

Thanks for the suggestion! I'll check out the "Group" settings and see if I can't use that somehow.

Unfortunately, the billing section has to stay the same. We've run through every possibility including having people just write out the numbers they need to bill for, but the system was just too complicated. We want that half of the form to remain constant.

 

[AlphaFrog]

This macro will sort the patients by room number.

• Assumes the room numbers are in cells A2, A8, A14, A20...etc.
• It groups every six rows.
• It temporarily uses a empty helper column (column AD or column #30).
• You must set the worksheet's protection password in the code to suit.

Sub Sort_Patients_by_Room()

    Const col As Long = 30  [COLOR="Green"]' Helper column; 30 = Column "AD"[/COLOR]
    Dim i As Long

    ActiveSheet.Protect [COLOR="Red"]Password:="Secret"[/COLOR], UserInterfaceOnly:=True
    
    Application.ScreenUpdating = False
    
    For i = 2 To Cells.Find("*", , , , xlByRows, xlPrevious).Row Step 6
        Cells(i, col).Resize(6).Value = "=IF(R" & i & "C1="""",9999,R" & i & "C1)&TEXT(ROW(),""-0000"")"
    Next i
    
    Columns("A:A").Resize(, col).Sort Key1:=Cells(2, col), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        
    Columns(col).ClearContents
    
    Application.ScreenUpdating = True

End Sub

You could assign this macro to a command button on the worksheet and the user could click it after adding\deleting patients.

 

[hortoncj]

You could assign this macro to a command button on the worksheet and the user could click it after adding\deleting patients.

Thanks for your suggestion! Unfortunately I'm not that savvy when it comes to Macros. Is there any way you could be a bit more descriptive as to how I would go about incorporating this feature?

 

[AlphaFrog]

Run a Macro from a Command Button
http://www.mrexcel.com/tip068.shtml

 

[hortoncj]

The macro does sort the information the way I need it to, thank you. I have a couple of questions, however:

I'm getting the cells on the right half with patient names in them (i.e. "gaga", "bush", etc.). Is there any way I can stop that from happening? Also, can this be expanded to include multiple sheets? We often have far more than 6 patients in a work area. I need the sorting to cover up to three pages, if possible.

Thanks again!

 

[AlphaFrog]

I'm getting the cells on the right half with patient names in them (i.e. "gaga", "bush", etc.).

I don't understand.

Also, can this be expanded to include multiple sheets? We often have far more than 6 patients in a work area. I need the sorting to cover up to three pages, if possible.

You want to sort all the patients from multiple worksheets? If yes, it could be done but would require a much more elaborate macro. More than I'm willing to do.

 

[Taul]

Just to clarify,
hortoncj kindly sent me a copy of the workbook so I could add your code and link it with a command button, as the op stated “I'm not that savvy when it comes to Macros”

As the right hand fields were identical, I added extra words in them manually so as to demonstrate that the code sorted them correctly. These extra words like gaga, bush etc can simply be deleted, as they were the patients name for each room.
My apologies hortoncj, I forgot to explain why I added the extra words manually.

The sorting code works wonderfully.
(I can foresee a use for it soon, so if you don’t mind I’ll keep a copy for myself)

cheers
Paul h