software

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 IJK….
MemphisSmithSmithSmithDoe
IndianapolisBrownBrownBrownBrown
MiamiDoeDoeDoeJay

2nd spreadsheet is the name of the employee and then their base city
where they would travel from:
Column BColumn C
BrownIndianapolis
DoeMiami
JayNewark
SmithNashville

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: www.excelforum.com/member.php...oamp;userid=30972
View this thread: www.excelforum.com/showthread...hreadid=516221I think to begin with you would need to build up a table of city names
against city names, indicating how many days travel are needed between
each city - something like:

Indianapolis Memphis Miami Nashville Newark
Indianapolis 0
Memphis 0 1 4
Miami 1 0
1
Nashville 4 0
Newark 1
0

I've only been able to build these up from your examples above -
obviously, you would want this to be as complete as possible (I think
I've got them right). This table could be placed in a third worksheet -
call it quot;ref_dataquot;.

You haven't indicated where your travel time should be placed - assume
it is in another worksheet called quot;Travel_timesquot;, where you would also
have a list of employees. A formula in here could try to find a match
with the name in column H of Sheet1 (using MATCH( ) ), and then with
the INDEX( ) function you could determine which town that employee is
meant to work that week. Then using your table of travel times, it
would be possible to return the travel time to the fourth sheet - it
would be quite a long formula (which could then be copied to the other
cells on that sheet), but I would need a few more details before
attempting it. Such as: how many employees, how many cities, how many
weeks of scheduling, can you complete the travel-times table and where
is it located, etc ??

Hope this helps for now ...

PeteSorry about the line wrap on two of the lines in the table.

PeteLet'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, quot;Sheet1quot;)
if we change X by the match formula above:
=address(match(Sheet2!C2, Sheet1!A:A, 0), 1, 1, 1, quot;Sheet1quot;)

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, quot;Sheet1quot;),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,
quot;Sheet1quot;),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,
quot;Sheet1quot;),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,
quot;Sheet1quot;),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 - Brazilquot;Karmenquot; wrote:

gt;
gt; Hello,
gt; I have a problem, which I cannot seem to sort out. I have
gt; two spreadsheets. The first spreadsheet, column C is a list of cities,
gt; and columns H through AC is a list of dates under which employee names
gt; are assigned. The employee does not always work in the same city and I
gt; would like to display on the second spreadsheet how many times a
gt; particular employee is in a particular city. However it is then more
gt; difficult, as I have the employee name and their base city location on
gt; the second spreadsheet and I would like to compare the city where the
gt; employee is scheduled to work on the first sheet to where they are
gt; based on the second sheet to determine if travel is required.
gt; For example:
gt; 1st spreadsheet is the city scheduled to work and names under dates
gt; scheduled to work:
gt; column C Column H IJK….
gt; MemphisSmithSmithSmithDoe
gt; IndianapolisBrownBrownBrownBrown
gt; MiamiDoeDoeDoeJay
gt;
gt; 2nd spreadsheet is the name of the employee and then their base city
gt; where they would travel from:
gt; Column BColumn C
gt; BrownIndianapolis
gt; DoeMiami
gt; JayNewark
gt; SmithNashville
gt;
gt; In this case Brown does not need travel
gt; Doe needs 1 day travel
gt; Jay needs 1 day travel
gt; Smith needs 4 days travel
gt;
gt; Is there a formula that will display how may days the employee needs
gt; travel based on the above information?
gt;
gt;
gt; --
gt; Karmen
gt; ------------------------------------------------------------------------
gt; Karmen's Profile: www.excelforum.com/member.php...oamp;userid=30972
gt; View this thread: www.excelforum.com/showthread...hreadid=516221
gt;
gt;


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

Rogerio Takejame Wrote:
gt; Let's split your solution in parts:
gt;
gt; 1) Where is the city of Sheet2!C2, for example, in Sheet1? The
gt; following
gt; formula answers it:
gt; =match(Sheet2!C2, Sheet1!A:A, 0)
gt; if the city is Indianapolis, the formula result will be 2
gt;
gt; 2) What is the address of the first cells of Sheet1, row X, where X is
gt; the
gt; result of the formula above? It will be:
gt; =address(X, 1, 1, 1, quot;Sheet1quot;)
gt; if we change X by the match formula above:
gt; =address(match(Sheet2!C2, Sheet1!A:A, 0), 1, 1, 1, quot;Sheet1quot;)
gt;
gt; 3) But the above formula gives us a text, not a cell reference. And
gt; now? Use
gt; the indirect formula:
gt; =indirect(address(match(Sheet2!C2, Sheet1!A:A, 0), 1, 1, 1,
gt; quot;Sheet1quot;),1)
gt;
gt; 4) What is the address of the row Sheet1!X:X where X is the number of
gt; the
gt; row of the first match formula? Use the offset function:
gt; =offset(indirect(address(match(Sheet2!C2, Sheet1!A:A, 0), 1, 1, 1,
gt; quot;Sheet1quot;),1), 0, 0, 1, 256)
gt;
gt; 5) How many times the name in Sheet2!C1 appears in Sheet1!X:X (where X
gt; is
gt; the result of the first match function)? Use this formula:
gt; = countif(offset(indirect(address(match(Sheet2!C2, Sheet1!A:A, 0), 1,
gt; 1, 1,
gt; quot;Sheet1quot;),1), 0, 0, 1, 256), Sheet2!C1)
gt;
gt; 6) How many times the name in Sheet2!C1 appears from column H to column
gt; AC?
gt; Use:
gt; =countif(Sheets1!H:AC, Sheet2!C1)
gt;
gt; 7) FINALLY, the number of travels will be the number of times that a
gt; name
gt; appears minus the number of times if appear in row X. In other words it
gt; will
gt; be part 6 minus part 5:
gt; =countif(Sheets1!H:AC, Sheet2!C1) -
gt; countif(offset(indirect(address(match(Sheet2!C2, Sheet1!A:A, 0), 1, 1,
gt; 1,
gt; quot;Sheet1quot;),1), 0, 0, 1, 256), Sheet2!C1)
gt;
gt; Reply me if it don't work. I work with Excel in portuguese and the name
gt; of
gt; my functions are different, but I think that I've translated right.
gt;
gt; --
gt; Rogerio Takejame
gt; Americana - Sao Paulo - Brazil
gt;
gt;
gt; quot;Karmenquot; wrote:
gt;
gt; gt;
gt; gt; Hello,
gt; gt; I have a problem, which I cannot seem to sort out. I have
gt; gt; two spreadsheets. The first spreadsheet, column C is a list of
gt; cities,
gt; gt; and columns H through AC is a list of dates under which employee
gt; names
gt; gt; are assigned. The employee does not always work in the same city and
gt; I
gt; gt; would like to display on the second spreadsheet how many times a
gt; gt; particular employee is in a particular city. However it is then
gt; more
gt; gt; difficult, as I have the employee name and their base city location
gt; on
gt; gt; the second spreadsheet and I would like to compare the city where
gt; the
gt; gt; employee is scheduled to work on the first sheet to where they are
gt; gt; based on the second sheet to determine if travel is required.
gt; gt; For example:
gt; gt; 1st spreadsheet is the city scheduled to work and names under dates
gt; gt; scheduled to work:
gt; gt; column C Column H IJK….
gt; gt; MemphisSmithSmithSmithDoe
gt; gt; IndianapolisBrownBrownBrownBrown
gt; gt; MiamiDoeDoeDoeJay
gt; gt;
gt; gt; 2nd spreadsheet is the name of the employee and then their base city
gt; gt; where they would travel from:
gt; gt; Column BColumn C
gt; gt; BrownIndianapolis
gt; gt; DoeMiami
gt; gt; JayNewark
gt; gt; SmithNashville
gt; gt;
gt; gt; In this case Brown does not need travel
gt; gt; Doe needs 1 day travel
gt; gt; Jay needs 1 day travel
gt; gt; Smith needs 4 days travel
gt; gt;
gt; gt; Is there a formula that will display how may days the employee needs
gt; gt; travel based on the above information?
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Karmen
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Karmen's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30972
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=516221
gt; gt;
gt; gt;--
Karmen
------------------------------------------------------------------------
Karmen's Profile: www.excelforum.com/member.php...oamp;userid=30972
View this thread: www.excelforum.com/showthread...hreadid=516221