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