I do the scheduling for our Communications Center. We have a bunch of different places and ways we display who is working when and schedules frequently change while new people are in training and moving between different shifts and furlough assignments.

Invariably, when there is a change, something gets missed and when it is found we already have a problem.

I have been making progress on keeping a “master” schedule where a name and furlough assignment are entered for a particular shift, then using that to populate the data into the different formats and places to automate the update process.

I’ve gotten pretty good (I think) at getting Excel to “count” blank cells or do things based on text instead of numeric data. I’ve got it doing what I want, but I am now trying to fine tune it and not waste space by showing furlough people on the daily roster.


My main data source is a sheet where I put a name and furlough assignment in for a given shift and it shows who works what days similar to this:

SHEET #1
NAME. FUR. SUN. MON. TUE. WED. THU. FRI. SAT
BOB. TW. BOB. BOB. F. F. BOB. BOB. BOB
SUE. MT. SUE. F. F. SUE. SUE. SUE. SUE
TIM. FS. TIM. TIM. TIM. TIM. TIM. F. F

I only enter data in the “Name” and “FUR” (furlough) columns, formulas than fill the name in under the day of the week if it is a work day or an “F” if the person is on furlough that day.



Using that data, I take the days from horizontal to vertical. With the “IF” function, I get it to do this:

SHEET #2
SUN: BOB SUE TIM
MON: BOB - TIM
TUE: - - TIM
WED: - SUE TIM
THU: BOB SUE TIM
FRI: BOB SUE -
SAT: BOB SUE -

If I make a change to the name or furlough assignment in Sheet #1, Sheet #2 is automatically updated



But what I would like to do is have Sheet #2 ONLY show who is working and skip those on furlough to reduce the number of cells across, something like this:


SUN
BOB SUE TIM

MON
BOB TIM

TUE
TIM

WED
SUE TIM

THU
BOB SUE TIM

FRI
BOB SUE

SAT
BOB SUE

So basically, what I am trying to do is have Excel look at a vertical range and use data meeting a text criteria to fill a horizontal range.

Can this be done? If not directly, maybe having an intermediate step? Vertical all to vertical working, then vertical working to horizontal working?



This is overly simplified example, with as many as 15-18 people working at a time and needing to list the hours as well (in overlapping 4 hour blocks), having a bunch of blank cells is annoying and results in scrolling


A more accurate example of how Sheet #2 currently looks would be this:

SUN
0500-0900. Tim. Bob. - Sally. Sue. - - Pete. Joan Art. Sam
0900-1300. NAME. - NAME NAME - NAME NAME NAME - -
1300-1700. — NAME NAME NAME — — - NAME NAME NAME
1700-2100. Etc...
2100-0100. Etc...
0100-0500. Etc...

Can you not just create a Pivot Table with your data input as the source? Filter out furlough, organize it as you wish?

If all your intention is to reduce the number of cells across, it may not be worth the effort for you.

It's not a matter of reducing cells since you need to allocate the maximum number of cells for the maximum number of people possible.

In Sheet 2, for each cell across using the nomenclature: Day - Shift #: Person 1, Person2, Person 3, etc.

For example for any given Day - Shift row in the cell for Person 1, the formula would have to see if Name 1 was scheduled and if schduled then show Name 1 from sheet 1 in Person 1 Cell sheet 2 otherwise If Name 1 was on furlough, then look at Name 2 if scheduled and if scheduled, then show Name 2 from sheet 1 in Person 1 Cell sheet 2 and so on.

For Person 2 cell, look at the name in Person 1 cell, then see if the name after the name in Person 1 cell is scheduled and if scheduled, then show name in Person 2 cell, otherwise, look at the next name if scheduled and if scheduled, then show the next name.

And the end of the formula for each Person cell is if the previous Person cell is NULL/BLANK, then we've reached the end of the list of names available so we have to show NULL/BLANK too.

I know what I speak of, I'm an Excel Whisperer: See the picture of a work calendar I developed.

Each day in the calendar shows only the events scheduled for that day. Look at the formula that is in the formula bar. The whole formula is not showing. But the logic is the same: If there's nothing there show "" but if there's something there then show it then go to the next event name and if it's scheduled, show the event otherwise show "" then go to the next event and so on and so forth.

EDITED TO ADD: you could just use one cell with one formula that will show all the people working for that day - shift combination. The formula will be similar to the calendar below. You'll just have to wrap the names for each row like how each event is listed on it's own line inside each cell. But like I said, the formula will be as I describe it above.

quote:

Excel Whisperer

Yes you are!!!

quote:

Originally posted by craigcpa:

quote:

Excel Whisperer

Yes you are!!!

Thank you, Craig. I appreciate it.

Now, if I can just get people to agree I’m handsome and debonair....