Scoprion23

I'm a manager at a hockey arena and trying to simplify my scheduling process but not sure on how or if I'm able to do a few things I'd like to 1 being want to click the links on the Schedule Sheet and get basically the Calender sheet to input the person i clicks shifts, 2 some sort of way of simplizing the Schedule area to fit a full years worth of dates I know how to code them but as you can see on the sheet it takes a lot of space and if I"m able to do something other than 52 tabs that'd be nice. 3rd was I would like excel to put up a you are over staffed thing if I staff to many people for a certain job. Thanks to anyone who could help

Attached Files

Book1.zip (20.6 KB, 6 views)

gistek

1. I'd create separate excel files for each type of table, then use Insert Hyperlink to open the calendar from the schedule table. As long as both (all) tables are open, you can link them just as you would sheets in the same workbook, and they will auto adjust formulas if rows, columns, or cells are added, deleted or moved.

2. Try turning your column labels (the dates) sideways and formatting the data to less precision, arial narrow, and smaller text. As an alternate to the smaller text, you can reduce your view zoom to 75% Your column width, 10.71; Sideways labels, lower precision time at 8pt Ariel Narrow font, 6

3. In the list of who is staffed for a particular job use the COUNTIF function to count the number of employees assigned for that job.

COUNTIF(B$3:B$25,"Soda") will return the number of people assigned to Soda.

Putting the $ in allows you to copy the function to other cells in that column and maintain the correct search range. Then all you have to do is change the "Soda" to the other jobs. Be sure to keep your spelling consistant in your list, so all incidents are counted. "Soda" will not count "Sada"

Also, label the rows, so you can see what's being counted.

There is an advanced command, CONCATENATE, that can create the search term from the label field.

I have found Excel help to be very good if you know what search terms to enter.

Scoprion23

I already have the 1st one done that way I would just like to have it the other way if I'm able to. I do like your advice on the 3rd one I'll be sure to try that out tomorrow. Ya just not sure exactly what terms I was looking for to being with I know just enough to get me by in excel. But thanks for the help

ShosMeister

I've updated the calendar for you. I tested it with Colby and Scott but left the rest of the hyperlinks as they were.

The Calendar will build itself by adjusting the Current Year and Current Month on the Calendar 2 sheet. The rest of the values on there are calculated.

When you click on a hyperlink, the macro event is triggered and checks to make sure it comes from the Schedule sheet first. If so, it simply puts in the row number of the person you clicked on into the Schedule Row value on the Calendar 2 sheet. That will automatically fill in all of the data for that individual.

Hope it helps a bit. Let me know if you have any problems when you get more data filled in.

Attached Files

book1.zip (35.8 KB, 2 views)

Scoprion23

Well only problem i've had so far was that when i go passed the 1st week the in and out dates dont show up on the calender. But think I got it worked out that I can correct it

Scoprion23

K got that fixed idk what the other part of the code was that you had on there but wasn't reporting nothing as far as in and out times went but thats okay. Wanted to know if you look on the schedule sheet on the Row above my in time itd report what event was happening that day is there any way to do that with this type of set up as ive fixed it up. I'd know how to code it the way i was before just not sure how to now

ShosMeister

The other code you are probably referring to is residual from when I used that as a simple schedule myself (very long ago). I just copied it over to your sheet and cleaned up a few of the calculations. Probably missed a few. You should be able to delete it without any problems.

If you attach a copy of your updated sheet, I'll take a look at it to get it to show the event. There's enough room there for it so it shouldn't be a problem.

If you're still having problems with the in/out times showing up properly, include a few more in the sample and I'll get that sorted out too.

Did you get all of the links to work properly? I had only setup 2 of them.

Scoprion23

Ya I got the in's, out's and hyperlinks set up. only thing i dont is the event and the max people for a event but thats not huge but would be nice.

Attached Files

Book1.zip (32.5 KB, 2 views)

ShosMeister

It would help knowing where the events are going to be and if you could explain a bit more on what you mean by max people for an event and I'd be happy to help.

Scoprion23

Well the events I'd like in the cell to the left of the date. and I mean like obviously we only need X ammount of people for a maineiacs game and want it so that if i try to set up to many people it'd say overstaffed or something along those lines. Example would be we need 5 cashiers, 2 cooks, 3 soda people 4 runners 1 food prep and a dippin dots person which gives a total of 15 so if i schedule 19 we obviously would be paying for 4 more people when we dont have to. You can find the positions that people are normally at on the employees wksht

gistek

hmmm.

You might need a look-up table with positions (cashiers, cooks, etc) on one axis and events on the other. Then fill in the table with the number of people needed in each position for each event.

Then you create a LOOKUP function that pulls its information based on the event name.

I've had a very busy week so far and it looks as if it's not going to settle down anytime soon, otherwise I'd fiddle around with the concept and let you know what I find out.

You could try checking the LOOKUP, HLOOKUP, and VLOOKUP commands in Help.