Automatic dates for one year

[jazzkid]

I have been asked by a healthcare facility if there is a way to create a daily spreadsheet with the date automatically populated - either at the top of the form, or on the tab.

Maybe this needs to be done as a 3 or six monthly sheet - not sure how stable it would be with 365 tabs if that is the way to go. They would like the date to populate on each tab or sheet automatically so they have all the staff changes for the year. Due to the large numbers of staff and the 24/7 service this is a critical document for them and needs to be accurate and easily understood.

An example can be found at http://bit.ly/autodates

Thanks for any assistance.

Sue

 

[NARAYANK991]

Hi Sue ,

Two points , the first of which is not related to your question :

1. Since the new-look forum went live , the facility to upload files as a part of your question is available ; I have not posted a question , so I do not know where the button to upload a file is , but when I post a reply , the button to Upload a File is just next to the Post Reply button. Can you look around and see if you can find it ? It will make it easier for others to access your file.

2. I do not know why you need different tabs for each date in a year ; how much of data will be on each tab ? Even if it is a 1000 rows of data , all 365,000 rows can be accommodated in one tab.

When designing a system , ensure that all your data is in one place , as far as possible ; generating any kind of report is easier if all of the data is together , whether it is by using pivot tables or by formulae or by VBA. Having it on different tabs makes everything that much more difficult.

Narayan

 

[bobhc]

http://chandoo.org/forum/threads/file-upload.11644/

 

[jazzkid]

Thanks Narayan

As they had asked for a separate page for each day, I thought a tab would be the best way of doing this.

The use of the file is two-fold
1. For administrative staff to input data
2. For nursing staff to be provided with printouts of one page for each day.

There can be as many as 15 changes for one day, and this information is printed and provided to each area. Hence the requirement (from their perspective) to have separate tabs.

Not sure how this would go if all on the one tab due to the staff's understanding of software - even very basic things. Will be a bit of a learning curve. I have been there for a while - but only there for another 8 days. They will have to manage with no real computer understanding once I leave.

Thanks too for the info about the uploading files. I did see that on some questions and responses, and can see below with the "upload a file" icon. I haven't been here for a while and the update happened while I wasn't watching.

Regards

Sue.

Hi Sue ,

Two points , the first of which is not related to your question :

1. Since the new-look forum went live , the facility to upload files as a part of your question is available ; I have not posted a question , so I do not know where the button to upload a file is , but when I post a reply , the button to Upload a File is just next to the Post Reply button. Can you look around and see if you can find it ? It will make it easier for others to access your file.

2. I do not know why you need different tabs for each date in a year ; how much of data will be on each tab ? Even if it is a 1000 rows of data , all 365,000 rows can be accommodated in one tab.

When designing a system , ensure that all your data is in one place , as far as possible ; generating any kind of report is easier if all of the data is together , whether it is by using pivot tables or by formulae or by VBA. Having it on different tabs makes everything that much more difficult.

Narayan

 

[NARAYANK991]

Hi Sue ,

Since you say the hospital staff will need to have a user-friendly system , all the more reason to have everything on one tab ; let the person doing the data entry do just that without having to bother about changing tabs and other things.

If you can list the main functions of the system viz. data entry , report generation , query ,... along with all the options that they will need in each of the above 3 categories , then a simple easy-to-use system can be developed with enough data validation so that it becomes pretty robust.

The main thing is to talk to as many of the users as possible , and find out what they expect from the system ; what will they do while using the system , and how do they want the system to respond.

You can have one button to open a data entry form , and another to print out the day's schedule.

If you can upload a sample file with data for say a week , developing such a system is a matter of 3 or 4 days , given everyone's busy schedule ! Otherwise , it can be done in a matter of hours.

Narayan

 

[jazzkid]

Thanks Narayan

What you are suggesting is great - I just don't know how to create that. Tomorrow I will ask who needs to do what with the system.

My understanding is that the form will be completed each time someone calls and says they are unable to come to work. The person taking the call will fill in the information as listed in the uploaded file.

The person may call in today - but be unable to work next week, so it needs to be able to be versatile enough for someone to fill in the information perhaps a week ahead.

During each day, the information for the remainder of that day will be printed out for the staff to be aware of for the remainder of the day. Not sure if I am explaining this well enough. The reason it needs to be printed out is that the nursing staff using it after hours, do not go to the computer on a frequent basis as they are caring for patients. However, they will fill in the information on the printed for for the next day.

If, as you say, "You can have one button to open a data entry form , and another to print out the day's schedule." that would be great.

When you say "If you can upload a sample file with data for say a week..." I assume that you want the information in a sample file so you can understand the system - is that correct?

Thanks for your patience.

Sue

 

[NARAYANK991]

Hi Sue ,

I'll just put down my thoughts ; please clarify / confirm.

1. Column B ( Date and Time phoned in ) is an absolutely random date + time item , since it can be any valid value ; can I safely assume that it cannot be earlier than yesterday ( or today ? ) and it cannot also be later than now ( date + time ) ? These two limits can be used to validate the data entry.

2. Column C ( Staff member ) - can we have a complete database of staff members , so that the data entry can be done using a Data Validation drop-down ?

3. Column D ( Date and Time of Shift ) - can we have a complete list of shifts , so that the selection is done from that list ?

4. Column E ( Reason ) is just text ; the only validation can possibly be that it should not be left blank.

5. Column F ( Replaced By ) - will it be another Staff Member ? If so , selection can again be from a DV drop-down ; the only data validation can be that the name entered in (2) and (5) cannot be the same.

6. Columns G , H and I - can you throw more light on what kind of data will be entered in these ?

Narayan

 

[jazzkid]

Thanks Narayan

You are right with columns B to F. Following you numbering:

6. G is for the shift the staff member was to work. This will generally be AM PM or ND (Morning, afternoon or night)

7. H - is for the name of the person who took the call from the person ringing to say they are sick.

8. I - did the person in charge change the original roster to show that "C" is sick and "F" replace them.

So from your earlier comments,
6 - G can be a DV drop-down

7 - H can also be DV - with a list of people who are in charge - the person taking the call - this again would be that the name entered in (7) and (5) cannot be the same. Although if they became ill during their shift and were replacing themselves for the following day it is a possibility - although would be unusual.

8 - I would be "Yes" or "No" Possibly DV would be good to reduce the possibility of incorrect entry.

Thanks for the clear outline of this.

I can input data tomorrow.

Sue

 

[NARAYANK991]

Hi Sue ,

Great. It would be nice if you could discuss all of this with at least some of the users , so that they also feel involved in the entire process. Their observations may confirm or clarify some of these points.

Narayan

 

[jazzkid]

Hi Narayan
Can I input some data now so that I can show them what it would look like when they use and/or print it? As I don't fully understand, they won't understand what I am saying?

Thanks Sue

 

[jazzkid]

Hi Narayan

I have put some data in the file - not the whole amount as a bit late here and need get some rest. I have named the data ranges for reference.

It would give us some idea of what this would do and how it would work.

Thanks for your assistance with this.

Sue

 

[NARAYANK991]

Hi Sue ,

Use the file now ; the columns C , F , G , H and I have the DV in them. The named ranges are dynamic , and will extend to include any fresh data that you add.

Narayan