Help with IF function for a schedule

[laurentwatts]

Hello,

I am fairly new to the IF function but would like to simplify my scheduling process. Attached is the file I would like to use for my schedule.

Here is what I am trying to do.

FOH 6-12 Mar 14 is my master schedule. I want to only have to make changes to this sheet.

FOH Team Sheet is the daily sheet that is printed and used by the Manager to see who is on shift for that day (don't ask why it is just how they do it).

When I fill in the data on the FOH 6-12 Mar 14 (for example, cells A3 (name), A5 (position), B/C 3 (time). I want this to transfer over to FOH Team Sheet Cells A5, B5, C5 and D5. However the data will change depending who is on shift. I think I can use the IF function to tell the FOH team sheet to look for what ever name and fill in the position and time associated with that name. For example, Low Rent, Joe Montana, or Jimmy Doolittle could work the EXPO 1 position, therefore A5 might change depending who is working that day, the same for A6 and A7.

Can anyone help me with this?

[yay_excel]

I actually do not believe what you are asking is possible with an IF function the way your data is currently formatted. I could be wrong!

[yay_excel]

It's definitely possible with a macro.

[laurentwatts]

I am not familiar with Macro's.

[laurentwatts]

Up dated form to hopefully be able to use macros.

[yay_excel]

Nicely done. I made a couple of changes:

1) I forgot to mention that no column headers should be merged. In fact, if you are going to get into macros it is best to completely avoid merging cells as merged cells can be tricky to work with. You can achieve the same effect by using Center Across Selection alignment.
2) I unmerged the headers and gave each column header a unique name. A good database should avoid having multiple fields with the same name as it becomes a lot more difficult to programmatically reference the data you are seeking if there are multiple places it could be.
3) I moved the WAGE column to be with the employee name and position as it made sense to me to keep that data together. This is not necessary.

[yay_excel]

Oh yeah, I also added a named range for the EMPLOYEES list so that we can just call it EMPLOYEES in our code rather than A2:Awherever it ends. I will help with the macro bit in a few.

[laurentwatts]

I see the changes. Do I need to make any changes to the second worksheet?

[yay_excel]

We should definitely separate the DAY and DATE labels from their corresponding values instead of keeping the label and the value in the same cell. It will be much easier to work with if we do not have to programmatically separate the date info from the rest of the text. In A2 I would put the label. In B2, I would use a formula like =CHOOSE(WEEKDAY(TODAY()),"SUNDAY","MONDAY","TUESDAY","WEDNESDAY","THURSDAY","FRIDAY","SATURDAY") but instead of TODAY() I would reference the cell which will contain the date once we separate it from the label. Note that your date will have to be formatted as a proper Excel date for the WEEKDAY function to work.

We will add some named ranges to the sheet as well but it's more important that the base data be formatted a certain way. We are going to be specific in the code about where the data goes, but the location of the data is going to vary depending on the day so it's important we are able to find the information easily. The named ranges I would suggest we add to the Team Sheet page are AMEMPLOYEES and PMEMPLOYEES.

I will probably not give more instructions until tomorrow as I am finished working today. Talk to you soon!

[laurentwatts]

I figured out how to use the IF function, FINALLY! I am still interested in the Macros though. Once I get the IF function file completed I will upload