Help getting multiple results from multiple criteria on multiple pages

**ERaasio** · 06-27-2013, 04:40 PM

I hope someone can help with this.

I have a workbook with 3 spreadsheets. Sheet1 has a drop down that selects a time period that I need the best agents working. The times are located in cells B1(start time) and C1(end time) for the time I need to staff my best people.

I have a spread sheet(sheet2) that ranks agents by their performance from 1 to 500 with Agents name in col A and rank in Col B.

On Sheet3 I have their schedule. ColA is Agents name, ColB is Start Time, ColC is 1st Break, ColC is Lunch Start, ColD Lunch End, ColE 2nd Break, ColF End of Shift.

What I need is a formula in ColA of Sheet1 that looks at the selected times on Sheet1 and finds all the agents that are working during that time(from sheet3) and displays their name and rank(found on sheet2). I need it to also not display the names of anyone on lunch or break that falls betwen the selected times. If the Name and Rank can be displayed in one cell that would be great, but two is fine.

I have tried looking at Index and Vlookups and I am completed confussed at this point on how to get it to work since its using multiple critera.

Any help would be greatly appreciated.

**ERaasio** · 06-28-2013, 09:50 AM

Anyone have any information about what I can do for this problem?

I have attached an example sheet with 40 people to help

**aydeegee** · 06-29-2013, 03:09 PM

Hi ERaasio

OK so you've got me hooked. I think that you are right in arriving at the conclusion that index, match and vlookups will not work for this. I can work something out using VBA, but I've come across a problem already.

You mention breaks (and show them on sheet3). You do not say whether you show the start or end of the break and how long the break lasts.

I will work on the hypothesis that you have recorded the start of a 15 minute break.

Regards
Alastair

**aydeegee** · 06-29-2013, 06:26 PM

Hi ERaasio

Does the attached meet your requirements?

The duplicate names will not be allowed, but I appreciate that it is for anonymising (is that really a word?) the data.

You did not ask for any sorting on the list, so you do not have any, but it is a simple thing to sort by start time, name, ranking or anything else

Regards
Alastair

**aydeegee** · 06-29-2013, 06:31 PM

Oops pre-final version attached last post. Try this one

Regards
Alastair

**ERaasio** · 07-02-2013, 09:39 AM

Aydeegee it works great thank you. My boss wanted the ranks seperated, but I was able to modify your code to do it. There is one addition I was hoping you could help guide me through. I have found after putting in the real data, that if we have a new hire on the team that is not ranked yet, it throws an error in the find the name from the ranking sheet.

As I will not be the only person updating the schedules, is there an easy way to put in a message box that checks to see if the name is on the ranking sheet first. Since the check is coming during an For statement, I am not sure how to add message. It woudl help the message says "The name <insert name here> is not on the Ranking sheet. Please update Ranking or add name to bottom of the ranking." This way they don't call me at home if there is a problem.

Thanks in advance

**aydeegee** · 07-03-2013, 07:29 PM

Hi ERaasio

How remiss of me not to have realised that humans will be operating this!

I have updated the VBA to require employees to be ranked and also put the ranking in its own cell (your boss is obviously a very wise person

)

(I must ask - what sort of agents are we dealing with here?)

Regards
Alastair