Find the values base of another cell

Hi guys,

I have a real head ache with this one... I'm trying to automate a process but let me explain it in a high level...

I have a sheet called "DATA" which has all employes names in column A, their shifts in column B, their managers name in column C, and their mangers shift in column D.

I have 2 other sheets called "Location" and "Schedule". I put the mangers name in the "Location" sheet which puts it in the "Schedule" sheet. I need a formula that will find the employees name in the "Data" sheet and put it in the Schedule sheet depending on the mangers name in Column C from the "Schedule" sheet.

Also I'm putting 25 spots for every manager in the "Schedule" sheet but most managers only have 20 associates so I want this formula to only populate the employees that belong to that manager and leave the other spaces blank.

Below you will find two formulas that do some of what I need but not as describe above. PLEASE HELP... I think I got about 5 gray hairs alrady trying to make this work....

=IF(ISERROR(MATCH(C3,Data!$C$1:$C$2000,0)),"",INDEX(Data!$A$1:$A$2000,MATCH(C3,Data!$C$1:$C$2000,0)))

=IF(ISERROR(MATCH(C3,Data!$C$1:$C$2000,0)),"",INDEX(Data!$A$1:$A$2000,MATCH(C3,Data!$C$1:$C$2000,0)*AND(A4<>A3))

Hi,

Your first formula is correct and should give you what you need ...

The second one will never work because of the last element added ...
*AND(A4<>A3))

My guess would be you need a sumproduct() formula ...
Can you upload a zipped copy of your worksheet, ( say with only the top 5 lines of each sheet ) for a precise answer ...

HTH
Carim

Here is a very small sample... In Sheet1 (called Schedule) should find all the employees names that belong to that manager but every manager in the schedule sheet will have 25 spots and most mangers only have 20 employes so it needs to know where to stop.....

Let me know if you have any questions.

Hi,

Since I am not 100% sure about what you are looking for, in the attached file, you will find two possibilities :

1. Autofilter in sheet data which allows a view per manager

2. A pivot sheet which recaps all managers, shifts and employees ...

HTH
Carim

Thank you very much for the pivot table... that help me alot with another issue I was having...

However, what I'm looking for is a formula that I can put in Cell A1 to A25 (Employees name) and this formula should look at C1 (managers name) and find it in sheet name "Data" C1 to C2000 then return all 25 employees. If that manager does not have 25 employees then it should return how ever many he has and leave the other cells blank...

For example... I have 20 employees... my name will be in C1 to C25 but this formula I'm looking for should pull my employees names and fill A1 to A20.

I'm starting to feel this formula is not possible.... I can't even explain it correctly.....;-(

One way is to use this formula in Schedule!A1

=IF(COUNTIF(Data!C$1:C$2000,C1)>ROW()-ROW(A$1),INDEX(Data!A$1:A$2000,SMALL(IF(Data!C$1:C$2000=C1,ROW(Data!C$1:C$2000)-ROW(Data!C$1)+1),ROW()-ROW(A$1)+1)),"")

must be confirmed with CTRL+SHIFT+ENTER, i.e. put formula in cell, press F2 and then, whilst holding down CTRL and SHIFT keys, press ENTER. Curly braces like { and } will automatically appear around the formula in the formula bar.

copy formula down as far as necessary. If names run out then a blank will be shown

wow.... that worked great.... Thank you soooooooooooooooo much daddylonglegs.... and Carim too.

I've been trying to get a formula to work for almost 2 weeks now... (my brain does not even function anymore)...

and now that has been completed maybe you can help with this question ;-). Frist let me start by saying I think I'm going to need a VBA code but let me know what you think.

I have another sheet which has been driving me crazy for a month now... (please see attachment). I would like to know how to be able to have either a VBA code or formula that would

Lets say sheet1 is name "Schedules" and each row (A3 to A800) is a seat which is number in column A and in row 1 (from B2 to BV2) you see 7, 7.25, 7.5, 8, 8.25, all the way until 1 AM). In sheet2 I have employees name in column A and their workshift in column B and then a break out of their shifts in the same way as the schedule format (from E2 to BY2) with 7, 7.25, 7.5, 8, 8.25 all the way until 1 AM. I'm trying to have excel look in sheet2 and populate the value it finds in that time in sheet1 for all employees without any overlap. so if we have 2 employees one that works 7 to 3.75 that person would that a "t" in each column that represents that time. but I want excel to take that and put it in to sheet1 and if there is another employee that works 4 to 9 PM that it could use the same seat.... Please help me!!! I have attached a short sample of what I'm working with. Let me if there is a formula or a code maybe... I'll try anything ;-)

PS... as stated before my brain is not all here so if what I type does not make sence please let me know ;-(

Hi,

Just had a look at your sample2 sheet ...
I do not understand the seat allocation process ...?
Could you clarify ?

Carim

Thank you Carim for your reply...

The sample I attached is a very small part of a REALLY big floor plan/schedule sheet.

They way it works is we have 2 team areas which are about 40 seats and we assign 3 or 4 managers to that area which could have between 5 or 20 employees...

What I'm trying to do is to have a formula or macro to assign a seat to the schedule from sheet2 as in a first come first serve basis... it should not overlap but the same seat should be use if 1 person leaves and another comes in after the 1st person shift is over... Please see attached for a sample of the teams within 40 seats....

I also wanted to mention... if there is no room for someone’s shift then is ok not to populate it... so once the area is been filled with as many shifts was possible the other ones do not have to be place anywhere...

I hope that help.....

Thank you again for taking the time to look at this...