Thank you Rogerio.......
It worked!
Karmen:)

Rogerio Takejame Wrote:
Let's split your solution in parts:

1) Where is the city of Sheet2!C2, for example, in Sheet1? The
following
formula answers it:
=match(Sheet2!C2, Sheet1!A:A, 0)
if the city is Indianapolis, the formula result will be 2

2) What is the address of the first cells of Sheet1, row X, where X is
the
result of the formula above? It will be:
=address(X, 1, 1, 1, "Sheet1")
if we change X by the match formula above:
=address(match(Sheet2!C2, Sheet1!A:A, 0), 1, 1, 1, "Sheet1")

3) But the above formula gives us a text, not a cell reference. And
now? Use
the indirect formula:
=indirect(address(match(Sheet2!C2, Sheet1!A:A, 0), 1, 1, 1,
"Sheet1"),1)

4) What is the address of the row Sheet1!X:X where X is the number of
the
row of the first match formula? Use the offset function:
=offset(indirect(address(match(Sheet2!C2, Sheet1!A:A, 0), 1, 1, 1,
"Sheet1"),1), 0, 0, 1, 256)

5) How many times the name in Sheet2!C1 appears in Sheet1!X:X (where X
is
the result of the first match function)? Use this formula:
= countif(offset(indirect(address(match(Sheet2!C2, Sheet1!A:A, 0), 1,
1, 1,
"Sheet1"),1), 0, 0, 1, 256), Sheet2!C1)

6) How many times the name in Sheet2!C1 appears from column H to column
AC?
Use:
=countif(Sheets1!H:AC, Sheet2!C1)

7) FINALLY, the number of travels will be the number of times that a
name
appears minus the number of times if appear in row X. In other words it
will
be part 6 minus part 5:
=countif(Sheets1!H:AC, Sheet2!C1) -
countif(offset(indirect(address(match(Sheet2!C2, Sheet1!A:A, 0), 1, 1,
1,
"Sheet1"),1), 0, 0, 1, 256), Sheet2!C1)

Reply me if it don't work. I work with Excel in portuguese and the name
of
my functions are different, but I think that I've translated right.

--
Rogerio Takejame
Americana - Sao Paulo - Brazil


"Karmen" wrote:


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?


--
Karmen

------------------------------------------------------------------------
Karmen's Profile:
http://www.excelforum.com/member.php...o&userid=30972
View this thread:
http://www.excelforum.com/showthread...hreadid=516221




--
Karmen
------------------------------------------------------------------------
Karmen's Profile: http://www.excelforum.com/member.php...o&userid=30972
View this thread: http://www.excelforum.com/showthread...hreadid=516221