VLOOKUP across sheets

D

Drahmee

New Member
Joined
Feb 1, 2015
Messages
18
Hi All,

This project is way above my abilities, so I'm asking for help.


Sheet 1 must contain the following data, in the following manner:

The employee's name in Column B, their Department in Column C, the day of the week in column D, their start time in Column E (on 24 hour clock), and their finish time in column F (on 24 hour clock). These five pieces of info must be listed in this manner, for a specific reason.

The sheets are set up in the following way:
Sheet 1 column titles (Columns B-F): Name, Department, Day of Week, Start Time, Finish Time
Sheet 2 and Sheet 3 column titles (Columns A-C): Sales, Shipping, Clerical
Sheet 2 and Sheet 3 row info (Rows 1-24): 00:00 - 01:00, 01:00 - 02:00, etc

If I want to use data from Sheet 1 to calculate staffing numbers for all Sales staff who are working 00:00-01:00, and for it to be displayed in Sheet 2 cell A1, what formula would I need? Please note that some of these staff members would start before and/or finish after these times.

If I want to use data from Sheet 1 to display staff names for all Sales staff who are working 00:00-01:00, and for it to be displayed in Sheet 3 cell A1, what formula would I need? Please note that some of these staff members would start before and/or finish after these times.


Thanks in advance for the help!

Doug
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Drahmee said:
Hi All,

Sheet 2 and Sheet 3 column titles (Columns A-C): Sales, Shipping, Clerical
Sheet 2 and Sheet 3 row info (Rows 1-24): 00:00 - 01:00, 01:00 - 02:00, etc
Click to expand...

Question/Comment: Where in sheets 2 and 3 is the timestamp? (00:00 - 01:00, 01:00 - 02:00, etc), you mention it is on rows 1-24 but what column? Note this may note be valid as Rows 1-24 maybe 2-25?

Drahmee said:
Hi All,

If I want to use data from Sheet 1 to calculate staffing numbers for all Sales staff who are working 00:00-01:00, and for it to be displayed in Sheet 2 cell A1, what formula would I need? Please note that some of these staff members would start before and/or finish after these times.

If I want to use data from Sheet 1 to display staff names for all Sales staff who are working 00:00-01:00, and for it to be displayed in Sheet 3 cell A1, what formula would I need? Please note that some of these staff members would start before and/or finish after these times.
Click to expand...

Question/Comment: You mention putting this in Sheets 2 and 3 in Cell A1 however you also indicated that there should be a header "Sales" in Column A which would be in conflict?

Please advise.
 
Last edited:
Upvote 0
Coding4Fun said:
Question/Comment: Where in sheets 2 and 3 is the timestamp? (00:00 - 01:00, 01:00 - 02:00, etc), you mention it is on rows 1-24 but what column? Note this may note be valid as Rows 1-24 maybe 2-25?

Timestamp in column A



Question/Comment: You mention putting this in Sheets 2 and 3 in Cell A1 however you also indicated that there should be a header "Sales" in Column A which would be in conflict?

Header would be in row 1

Please advise.
Click to expand...


Thanks!
 
Upvote 0
Sheet 1 is fine as described. Sheet 2 you would want to add a time stamp column (Column A).

Columns A - D / Headers: A:TimeStamp, B:Sales, C:Shipping, D:Clerical

The following would be inserted into B2

Code: 
=COUNTIFS(Sheet1!$C:$C,Sheet2!$B$1,Sheet1!$D:$D,"Monday",Sheet1!$E:$E, "<=" & TEXT(LEFT(Sheet2!$A2,5),"hh:mm"),Sheet1!$F:$F, ">=" & TEXT(RIGHT(Sheet2!$A2,5),"hh:mm"))

Copy this to C and D and then Change $B$1 to $C$1 and $D$1

Repeat the process on Sheet 3 (Not sure why 3 - Copy of 2 and 3)

Then on Sheet3 note that in the formula you need to change Sheet2 to Sheet3

You would also want to adjust "Monday" maybe to a cell that has the day in question. It also does not like hour 00:00 this is always off.
 
Last edited:
Upvote 0
Hi,

Sheet 2 is to calculate how many staff at each time, and Sheet 3 is to list the names of all the people who fit each criteria, preferably separated by a comma.

Thanks!
 
Upvote 0
Sheet 1 is the input sheet, Sheet 2 is the output sheet which displays the staffing levels as a number, and Sheet 3 is the output sheet which displays the names of those reflected in the numbers on Sheet 2.


Sheet 1:

A B C D E F
1NameDepartment Day Start time Finish Time Total hours
2DougSalesMonday60014008
3DougSalesTuesday60014008
4DougSalesWednesday60014008
5DougSalesThursday60014008
6DougSalesFriday60014008
7DougSalesSaturday60014008
8FrankClericalSunday120020008
9FrankClericalMonday120020008
10FrankClericalTuesday120020008
11FrankClericalWednesday120020008
12FrankClericalThursday120020008
13FrankClericalFriday120020008
14AngelaShippingSunday150023008
15AngelaShippingMonday150023008
16AngelaShippingTuesday150023008
17AngelaShippingWednesday150023008
18AngelaShippingThursday150023008
19AngelaShippingFriday150023008
20AngelaShippingSaturday150023008

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


Sheet 2 and 3:

A B C D E F G H I J K L M N O P Q R S T U V
1 Timestamp SalesShipping Clerical SalesShipping Clerical SalesShipping Clerical SalesShipping Clerical SalesShipping ClericalSalesShipping Clerical SalesShipping Clerical
2

Sunday SundaySundayMondayMondayMondayTuesdayTuesdayTuesdayWednesdayWednesdayWednesdayThursdayThursdayThursdayFridayFridayFridaySaturdaySaturdaySaturday
3 00:01-1:00
4 01:00-2:00
5 02:00-3:00
6 03:00-4:00
7 04:00-5:00
8 05:00-6:00
9 06:00-7:00
10 07:00-8:00
11 08:00-9:00
12 09:00-10:00
13 10:00-11:00
14 11:00-12:00
15 12:00-13:00
16 13:00-14:00
17 14:00-15:00
18 15:00-16:00
19 16:00-17:00
20 17:00-18:00
21 18:00-19:00
22 19:00-20:00
23 20:00-21:00
24 21:00-22:00
25 22:00-23:00
26 23:00-23:59

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="3"><col span="3"><col><col><col><col span="3"></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="3"><col span="3"><col><col><col><col span="3"></colgroup><tbody>
</tbody>
 
Upvote 0
Drahmee said:
Sheet 1 is the input sheet, Sheet 2 is the output sheet which displays the staffing levels as a number, and Sheet 3 is the output sheet which displays the names of those reflected in the numbers on Sheet 2.


Sheet 1:

A B C D E F
1NameDepartment Day Start time Finish Time Total hours
2DougSalesMonday60014008
3DougSalesTuesday60014008
4DougSalesWednesday60014008
5DougSalesThursday60014008
6DougSalesFriday60014008
7DougSalesSaturday60014008
8FrankClericalSunday120020008
9FrankClericalMonday120020008
10FrankClericalTuesday120020008
11FrankClericalWednesday120020008
12FrankClericalThursday120020008
13FrankClericalFriday120020008
14AngelaShippingSunday150023008
15AngelaShippingMonday150023008
16AngelaShippingTuesday150023008
17AngelaShippingWednesday150023008
18AngelaShippingThursday150023008
19AngelaShippingFriday150023008
20AngelaShippingSaturday150023008

<tbody>
</tbody>


Sheet 2 and 3:

A B C D E F G H I J K L M N O P Q R S T U V
1 Timestamp SalesShipping Clerical SalesShipping Clerical SalesShipping Clerical SalesShipping Clerical SalesShipping ClericalSalesShipping Clerical SalesShipping Clerical
2Sunday SundaySundayMondayMondayMondayTuesdayTuesdayTuesdayWednesdayWednesdayWednesdayThursdayThursdayThursdayFridayFridayFridaySaturdaySaturdaySaturday
300:01-1:00
401:00-2:00
502:00-3:00
603:00-4:00
704:00-5:00
805:00-6:00
906:00-7:00
1007:00-8:00
1108:00-9:00
1209:00-10:00
1310:00-11:00
1411:00-12:00
1512:00-13:00
1613:00-14:00
1714:00-15:00
1815:00-16:00
1916:00-17:00
2017:00-18:00
2118:00-19:00
2219:00-20:00
2320:00-21:00
2421:00-22:00
2522:00-23:00
2623:00-23:59

<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...


We need to break the "total hours" data into hourly "categories" (The categories being "100-200", "200-300" and so on.)!

To do this we use the columns G through AE in Sheet1! Use 1 as column G heading, 2359 as column AE heading and the rest of the columns would have the headings 200, 300 until 2300 from columns H till column AD!

Then in the cells G2 through AD20 (not AE20) use this formula -->
=IF($D2=G$1,1,IF(AND($D2<G$1,$E2>=H$1),1,0))

Since here the original data ends in 20th row, we should paste the formula until that row!

The hourly breakup data is now available!

Then we need to TRANSPOSE this data!
Insert a new Sheet, Sheet4.
Select 20 columns (From A through T) and 31 rows.
Then in cell A1 type
=TRANSPOSE(Sheet1!A1:AE20) and press Ctrl+Shift+Enter --> the way to enter an array formula!

In Sheet2, from cells B3 till V26 paste the formula
=SUMIFS(Sheet4!$B7:$T7,Sheet4!$B$2:$T$2,Sheet2!B$1,Sheet4!$B$3:$T$3,Sheet2!B$2)

Sheet2 would be done!

I'm unsure of the Sheet3! If the count in any of the cells would be more than 1, are we to display ALL the names in the corresponding cell? Or is it mandatory that the cell values won't be more than 1?
 
Upvote 0
KolGuyXcel said:
<g$1,$e2>

I'm unsure of the Sheet3! If the count in any of the cells would be more than 1, are we to display ALL the names in the corresponding cell? Or is it mandatory that the cell values won't be more than 1?
Click to expand...

The count in most cells will be above 1, and I'd like it to display all names, separated by a comma. </g$1,$e2>
 
Upvote 0
The hourly breakup data doesn't seem to transfer correctly. No formulas work correctly, even the first one. :(
 
Upvote 0
The above formula (=IF($D2=G$1,1,IF(AND($D2) is working fine with me, when I use the given data! The cell values in the columns D and E i.e. the ones with the headings Start Time and Finish Time, are numbers in multiples of hundreds and not in any time format! And the formula is working fine! However, if those values are in time format i.e. something like 6:00 etc. then this formula won't work. Also, when I'm using the above given data, I'm using the 1st row for the headers (Name, Department, Day etc.) and not the column numbers (1,2,3 etc.).
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Nested IF statements to analyze time data
Replies
6
Views
113
RobOrBob
R
corentint
  • Solved
A recursive procedure
Replies
3
Views
126
John_w
J
J
  • Question
Shift bands
2 3
Replies
29
Views
997
felixstraube
F
alm395
  • Question
Coverage By Hour
Replies
3
Views
180
alm395
alm395
C
  • Question
Ordinary Hours or Penalty Shift
Replies
0
Views
76
ChetanPuri
C

Forum statistics

Threads
1,215,043
Messages
6,122,822
Members
449,096
Latest member
Erald

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top