Help with INDEX for creation of scheduler worksheet

[su_maverick]

I am an assistant administrator for a group of doctors offices. We have multiple locations and our doctors are not always situated in the same location. I have been trying to create a scheduler program that will auto-populate a calendar for those locations. I wanted this to be as dynamic as possible so this is what I set up.

- The month would be pasted into the scheduler which would have things like vacations, holidays, and days not working already pre-populated from my paid-time-off tracking sheet.
- I would then work with the scheduler to ensure that all locations have the minimum number of employees working on it in a given day.

What I would like is for the resulting calendar (to be distributed to the employees) to have zero manual entry and take everything from the scheduler I am working on

Here is a sample of the file layout: http://s000.tinyupload.com/?file_id=11812568118096918644

This has the first week of the month setup as the calendar where it checks to see what the first day of the month is in the scheduler and dates it appropriately.

Ive been trying to find a way to use an INDEX function (or multiple ones) that would do the following
RESULT - Return the initials for the employee located in column C

Determining factor for initial index
ROW - The first initial would be the first occurrence that the office initials (O1, O2 etc) shows up on the scheduler
COLUMN - The date listed on the calendar

It would continue to do this for all occurrences of an employee being scheduled in that office on that day (I have been using the SMALL function for this

So, what I need it to do is go to the column for the date that I am piping into the equation and then search that column for the office initials I am going to give it. Once it has found that, I need to get the employee initials that are located in column C. This is why I feel that I need at least two index functions to accomplish this.



Here is my top layer INDEX function that give the employee initials:
INDEX($A$1:$AH$14,***FUNCTION THAT DETERMINES ROW***,3)

Here is what Ive been trying to work on for the nested INDEX to find the ROW:
=INDEX(A1:AH14,******,SMALL(IF($D$4:$AH$4=AN1,COLUMN(D4:AH4),""),COLUMN(1:1)))

Maybe Im doing this wrong. All I need it to do is return the row number(s) of the instances of the office initials showing up in the column specified by SMALL(IF($D$4:$AH$4=AN1,COLUMN(D4:AH4),""),COLUMN(1:1))

Any help would be appreciated.

 

[James006]

Hi,

Congratulations on what you have already accomplished on your own ...

Within your index() function, in order to determine the row number, you need to use the match() function ...

For example : =MATCH("E1",C1:C14,0)

HTH

 

[su_maverick]

Hi,

Congratulations on what you have already accomplished on your own ...

Within your index() function, in order to determine the row number, you need to use the match() function ...

For example : =MATCH("E1",C1:C14,0)

HTH

Thank you...

Here is the issue. I have been able to have it work perfectly using a very simplistic example but am having issues when I go to the more complex one that was in the original file I posted above. I added the simplistic one for people to test out and see what is causing the issue between that and the complex schedule on the first tab.

I just dont get it. The dynamic entries should be working with both simple and complex. Its a head scratcher.

Link to new file with example: http://s000.tinyupload.com/?file_id=07898654361783339428

 

[skywriter]

If you could reference the spreadsheet you posted and give some specific examples of what you want to see, I could probably help you. It's hard for me to tell what you want to do by just a description.

Something like, I would like a formula in this cell that looks at this data and returns this based on this and that. But please use actual cell references and I'm sure I or someone else can help you.

 

[su_maverick]

If you could reference the spreadsheet you posted and give some specific examples of what you want to see, I could probably help you. It's hard for me to tell what you want to do by just a description.

Something like, I would like a formula in this cell that looks at this data and returns this based on this and that. But please use actual cell references and I'm sure I or someone else can help you.

Sorry for the delay in response.

Here is an image of the 'scheduler' part of the page where you would manually enter in the office location for each day for each employee.

O1 - Office 1
O2 - Office 2
O3 - Office 3

Here is an image of the first week of the calendar and how it should be populating

For Friday, the first ROW for Office 1 would do the following:
- Check for the appropriate COLUMN on the scheduler by matching the date in CELL BH1 to the dates listed in row 4
- It would then look for the first instance of O1 in the COLUMN
- Once found, it will return the initials associated with that row in COLUMN C (E1)

For the next ROW for Office 1, it will do the following and find the 2nd instance of O1 which would be E2. It will keep checking until it does not find another instance of O1 in that column.

An example of how it is supposed to behave can be found in the v2 (second) file I posted above on the second tab. It is a much more simplistic version but it behaves exactly how I would like it to. I just cant seem to have it work the same way with the scheduler I created.

 

[James006]

Hi,

Had a quick look at the sheet named Complex within your workbook ...

I understand your final objective ... and before asking you a couple of questions,
I would like to suggest a few modifications :

1. Add the Year 2015 in cell A1
2. In cell D2, type =TEXT(WEEKDAY(DATE($A$1,MONTH($D$1&1),D4)),"ddd")
3. Copy this formula from D2 all the way to AH2
4. In cell D3, type ="W"&WEEKNUM(DATE($A$1,MONTH($D$1&1),D4))
5. Copy this formula from D3 all the way to AH3

Regarding the section which starts in cell AJ1 :
1. Do you plan to show the whole month or select weeks by their numbers ?
2. You have planned 4 Week Names ... shouldn't it be 5 ...in your example, there are 5 Fridays ...
3. Would you consider a VBA solution or not ?

HTH

 

[su_maverick]

Hi,

Had a quick look at the sheet named Complex within your workbook ...

I understand your final objective ... and before asking you a couple of questions,
I would like to suggest a few modifications :

1. Add the Year 2015 in cell A1
2. In cell D2, type =TEXT(WEEKDAY(DATE($A$1,MONTH($D$1&1),D4)),"ddd")
3. Copy this formula from D2 all the way to AH2
4. In cell D3, type ="W"&WEEKNUM(DATE($A$1,MONTH($D$1&1),D4))
5. Copy this formula from D3 all the way to AH3

Regarding the section which starts in cell AJ1 :
1. Do you plan to show the whole month or select weeks by their numbers ?
2. You have planned 4 Week Names ... shouldn't it be 5 ...in your example, there are 5 Fridays ...
3. Would you consider a VBA solution or not ?

HTH

1 - I just used the first week as the example. The calendar (which the whole reason for creation is so that it can be printed and distributed) will be the full month displayed over 5 week 'blocks'
2 - There will be a total of 5 week names. The W1 through W5 are a part of another calculation sheet that is included as part of a larger workbook
3 - I would consider VBA as long as it does not detract from the main function of the sheet which is the scheduler itself. The calendar is just a 'nice to have' that I am trying to work through right now.

 

[James006]

Added a macro to generate the schedule in your worksheet ...
http://http://s000.tinyupload.com/?file_id=86563900845855385002

All you need to update is year in cell A1 and month in cell D1...
then click on Schedule button ...

HTH

 

[su_maverick]

Added a macro to generate the schedule in your worksheet ...
http://http://s000.tinyupload.com/?file_id=86563900845855385002

All you need to update is year in cell A1 and month in cell D1...
then click on Schedule button ...

HTH

Hi James - Thank you very much. Could you let me know how you created the macro so that I can do so on my main sheet?

 

[James006]

Hi,

You are welcome ...

As long as the macro runs exactly as you want, the easiest solution is for you, once you haved opened the two files :

1. Go to VBA with Alt F11
2. From the menu Insert Module
3. Copy the macro from the sample workbook to yours

HTH