Hello,
I have a problem, which I cannot seem to sort out. I have two spreadsheets. The first spreadsheet, column C is a list of cities, and columns H through AC is a list of dates under which employee names are assigned. The employee does not always work in the same city and I would like to display on the second spreadsheet how many times a particular employee is in a particular city. However it is then more difficult, as I have the employee name and their base city location on the second spreadsheet and I would like to compare the city where the employee is scheduled to work on the first sheet to where they are based on the second sheet to determine if travel is required.
For example:
1st spreadsheet is the city scheduled to work and names under dates scheduled to work:
column C Column H I J K….
Memphis Smith Smith Smith Doe
Indianapolis Brown Brown Brown Brown
Miami Doe Doe Doe Jay

2nd spreadsheet is the name of the employee and then their base city where they would travel from:
Column B Column C
Brown Indianapolis
Doe Miami
Jay Newark
Smith Nashville

In this case Brown does not need travel
Doe needs 1 day travel
Jay needs 1 day travel
Smith needs 4 days travel

Is there a formula that will display how may days the employee needs travel based on the above information?