Scan schedule and populate cells with names.

[cbouley]

Hi everyone,

I have a scheduling dilemma. I create a traditional master schedule with columns being dates and rows being employees. There are many different areas in my workplace that need to know which employee is working that day.

So I need a formula or combination of formulas which scans a column and finds a string...ill use the example of the word 'day' as a string. It scans the column for the word day and if it finds it, it populates a different cell with the individuals name that is working that day,


I have made a sample xls file that describes my problem in greater detail.

Any help would be greatly appreciated.

[MarvinP]

Find the attached with the extra credit problem done.
It might be easier to keep the date shift table set and fill in names later.

Actually, I cheated and made your table more to my liking.

See if this works for you.

[cbouley]

Hey MArvin,
Thanks for the help. It has given me some very good information on what I want to do.
I can see how your method is much easier to harvest the data.
Unfortunately your method wont work for my situation. I scaled my schedule down to a 3 x 3 for simplicity. Its actually about 200 employees deep and 8 weeks wide and too many errors would result in them reading it that way. The day evening and night is also actually broken up into alphanumeric shift coding, it was just an example for simplicity.


So your code looks for an intersection and outputs the string at that intersection.

Is there anyway to look up 'day' (which will really be a code like D54 in real life) and identify which row its on then match that row with the B column. The names will always be on the B column.

In theory)
Scan Column C for string 'day'
Lookup which Row 'day' is on. Lets say row 8
Take that Row and intercept it with B column (B8)
take the string at B8 and populate it into another cell.

Ill keep working with what you gave me because it was a great help.

Thanks Again

[cbouley]

Looking back, your code would work perfectly if my table could be easily transformed into your table, as a middle step ( like a second worksheet that automatically took my format and made it yours)
But like I said, they are huge tables. approximately 200 x 50

I'm pretty sure any code that would transform my table into yours would be the same code I needed for the original problem.

I may just have to make both tables separately which is daunting because its so large.

[MarvinP]

Can you toss me a sample of your original table and let me see if it is easily transformable. It would a one time deal and give it back. Let me noodle on it a while.

[MarvinP]

I've added a new table at the bottom. I'm not admiring it much but it almost works. I bet we need one of those SumProduct wizzards to transform your existing worksheet to one that could be better?

See attached... Let me know if you want me to work on it a little more.