Asked By
elsietbrown
20 points
N/A
Posted on - 07/17/2012
Hi guys, I have been trying to solve a problem for a month that I have right now. All I look for is to create database table in sheet1, most likely along with drop down button (creating from name manager and validate cell along with list and the name right?).
I have more than 200 data and I am going to get by choosing designated technician in first column, required date in 2ndand specific time frame(could be 12am-12pm or 12pm-12am) in third column and I wish for getting the calculation of result over the sheet.
The major trouble is to get the idea of choosing the perfect rule for it in order to make it differentiated the 12 hrs frame of time and to calculate the data through the sheet and I hope I can try COUNTIF. Could you please provide something regarding this?
Thank you
Excel – Table Creation with some data
SHEET1 (SUMMARY SHEET) STEPS:
PUT HEADINGS THE “(X)” is the cell number: MANAGER(A1) ; NUMBER OF PERSONNEL(B1),SHIFT (12am-12pm) – (12pm-12am) (C1)
MANAGER
1. Click A2
2. Click the “DATA”, then look for “data validation”, a new window should open entitled “data validation” and should be on the “settings tab” (default).
3. Under the “settings tab” look for “Allow:” below that is a dropdown menu click on “List”
4. Then look for “Source:” (while still under the “settings tab”)
5. Below the “Source:” is where you select the cells to validate. Key in =Sheet2!A:A. By doing this you can input the manager name on sheet 2 without having to add anything on a list because the entire manager list is on that column because that’s where you input the names of the managers.
NUMBER OF PERSONNEL
1. Click B2 and type “=countif(Sheet2!A:A, A2)”. That should count the times the managers name appears in sheet 2
2. Drag down the formula to the last cell where there is an equivalent manager
SHIFT (12am-12pm) – (12pm-12am)
1. Type in “=COUNTIFS(Sheet2!A:A,Sheet1!A2,Sheet2!D:D,"12am-12pm")&"-"&COUNTIFS(Sheet2!A:A,Sheet1!A2,Sheet2!D:D,"12pm-12am")”
2. Drag down the formula to the last cell where there is an equivalent manager
SHEET2 (DATA SHEET) STEPS:
PUT HEADINGS THE “(X)” is the cell number: MANAGER(A1) ; PERSONNEL(B1),DATE REQUIRED (C1), SHIFT (12am-12pm) – (12pm-12am) (D1)
MANAGER
-
Below A1: Input the names of the managers
PERSONNEL
-
Below B1: Input the names of the personnel whom correspond to their managers in on A:A
DATE REQUIRED
-
Below C1: Input the date required
SHIFT (12am-12pm) – (12pm-12am)
-
Below D1: Input whether “12am-12pm” or “12pm-12am” the personnel works
I HOPE THIS HELPS.
