[OBP]

Bharath, do you intend putting data in to the "SampleBook" Worksheets in the Time Slots provided?
As that would probably be the best place to look for Nurses Currently booked.

[nynamyna]

Hi Anthony

Sorry I saw your post just now........only four details (surgeon name, surgery code, surgery type, equpiment) will be saved in the time slots available in the 'samplebook'.............as of now only two details will be saved in the but we can add that later.......all the other details entered will be saved in the excel sheet called 'Input file for patient details.xls' in order to print them easily.............

Thanks
Bharath

[OBP]

The Patient report Does Not include the Time Slot(s) of the Operation. It would need to to try and Calculate the Nurses in use over a given period.
It may be better to add it to the Input Worksheet, but not have it shown, say in Column X or AA.
This is going to take VBA to do the Calculation wherever it is stored and it won't be easy to do. It will have to take in to consideration any "overlapping" time slots and exclude them.

[nynamyna]

Hi Tony

wat you say is right but...The code already does not allow to schedule two surgeries at the same time .....try choosing same start time , duration and OR it will say that the OR is already occupied......

I have a sugesstion can we do like the following:

In the 'Input file for # of nurses.xls' we can add additional columns saying 'remaining # of nurses' and 'remaining # of equipments" we can populate both the columns with the same number in 'total number of nurses' and 'total number of equipments column' .....after that if we schedule a surgery by choosing a particular date in the drop down of the userform then it should read the 'remaining number of nurses' column and 'remaining number of equipments' column of the same date in the excel sheet (input file for number of nurses.xls).....so the remaining number of nurses and equipments columns should get updated according to the number of nurses scheduled (for eg:If there are a total of 10 nurses and 10 equipments then populate all the columns with 10 ........ if two nurses and two equipments are scheduled then the remaining number of nurses and equipments on that particular date should get reduced from 10 to 8........ so again if surgery is scheduled on the same date then it should read the column 'remaning number of nurses' and 'remaining number of equipments' of the same date which will have only 8 nurses and equipments.......
I have attached the modified excel file ' Input file for # of nurses with remaining'

I got this idea .....I dont know how far it is easy to execute......tell me your opinion

Thanks
Bharath

[nynamyna]

after keep on reducing if there is 0 remaining nurses and equipments and if someone asks for 1 or 3 nurses then a message box should open saying 'there is no nurses left for the scheduled date please choose an other date'......

[OBP]

Bharath, that is the sort of thing that I was talking about. I don't think that will work though, I will take a look today and see how it goes. The problem will be that it is not the Nurses Remaining for a Day, but for a Time Period. i.e. Nurses booked for 8:00 to 10:00 should then be available for 11:00 or do they only carry out 1 operation per day?

[OBP]

OK, this works, it means copying columns Z to AB when the Month is selcted.
It now puts the "days" nurses in the cell Z1 and stores the "used" nurses in column AA against the times booked.

[nynamyna]

Hi Tony

Thank you very much for the code.....I didn't even think about the time problem you said....Thanks for telling me the problem....your code does exactly what you said......I have a small doubt whether to put total number of nurses available at all time fixed or not? you have fixed it as 9 at all time right....I will think about that alone and get back to you ...........

Thank you
Bharath

[OBP]

Bharath, it is not fixed, it is updated from the other Workbook when you enter the Month, unless something is already there, which it wouldn't be if you had your 365 sheets.
Delete it and you will see what I mean.

[nynamyna]

No what I meant is that for all the time slots the total number of nurses will be the same initially right....tats wat I meant........


your code is perfect but I need some changes to be done.....I do not know how to do it

instead of the additional excel file 'Input for the # of nurses.xls' I would go with your idea of putting them by the side of all the 365 sheets but I do not know how to put those in all the 365 sheets. so that we can delete the combobox1(month) and combobox2(date) from the userform1 because it would be unecessary......

the nurses have a turnover time of half an hour (if there are a total of 9 nurses and if three nurses are used from 7 to 10 then the remaining should be 6 for next half an hour that is till 10.30)

Thanks
Bharath

[OBP]

Well you could have a Combo to select the nurses quantity and put that in Cell Z1 by having Z1 bound to the combo.
When you say you don't know how to put it on the 365 sheets, do you mean the Times and Formula?

[nynamyna]

ya I mean the times and the column headings 'nurses used' 'nurses available' 'total nurses available' so that it is automatically saved in all the 365 workshets......and that turnover problem......

[OBP]

You can copy the data from my sheet, highlight the first Excel worksheet tab, hold down the shift key and move to the last worksheet, that should highlight them all Paste the data in to cell Z1 and it should paste in them all.

[OBP]

Or
Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.

[nynamyna]

thanks....I thought we need to write a code......if you help me to add that turnover time of half an hour then the problem is solved......I realy thank you for the help.......