Confused

  • Thread starter Thread starter Karmen
  • Start date Start date
K

Karmen

Hello,
I have a problem, which I cannot seem to sort out.:confused: 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?
 
I 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 "ref_data".

You haven't indicated where your travel time should be placed - assume
it is in another worksheet called "Travel_times", 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 ...

Pete
 
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.
 
Thank you Rogerio.......
It worked!
Karmen:)

Rogerio said:
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.
Click to expand...
 
Back
Top