Enter Shift Patterns in Excel 2003 1

[Othalian]

Hi,

Do you like a challenge then here it is.

I am attempting to add a shift pattern to a holiday, course, etc spreadsheet. The shifts are Days, Evenings and Nights.
The Excel book has 12 tabs, one for each month of the year.
Cells A1-A3 on each spreadsheet are joined and have a controlling date for that month.
Cell B1 has the days title and, for Jan, the 31 cells to the right contain the relevant day of the week.
Cell B2 is the date title with again the 31 cells to the right containing the relevant date.
Cells to the right of B1 and B2 self populate when a date is placed in A1-A3 in the format 1/1/2008.

I have created a conditional format to add some gray verticle lines for Saturday and Sunday and again these update automatically when the year is changed.

Now the difficult part.

In Cell B3 is the shift title with the 31 cells to the right available to place the required shift. My difficulty is creating a formula that places the shift title in the required cells to the right of B3 and :
1. Checks what day it is.
2. Checks what the previous week's shift was.
3. Joins the cells for that week and adds text.
4. And also checks to see if the week carries over to the following month and correctly adds the text for that shift. For example if a day shift carries over by two days then the title days would have to be sited on the first three days and also on the two days of the following week.

I hope you are able to follow my description. This is a lot to ask for but some clever person will I'm sure come up with the answer.

I thank you in advance

 

[xlbo]

Your setup will give you issues

Splitting data is not the best way to begin

To store and calculate data you should use 1 worksheet for all the data. You can then REPORT on it how you want

What you are describing is how you would want to report on the data - not how you should store it in the 1st place

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Othalian]

Hi Xlbo,

Thanks for your reply. I have no real experience with Excel and what I know has been self taught so I bow to your wisdom when it comes to Excel, and probable other things too. ;-) The reason I set out the spreadsheet in this way was because that is how it was asked to be set out. I was going to set it out on one sheet. But none the less, I feel, I would still have arrived at the same point I am at now. How would I add the shift pattern between Sunday and Saturday for each week be it on one sheet of multiple sheets. You did suggest a report but the user needs to see on the one page that weeks shifts, (Days, Evenings or Nights) and who is on holiday, sick, ect. Lastly the user needs to have a printed copy for that month in case the computer fails so by setting it out on seperate pages simplifies it.

Thanks again for your response.