| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Help please, what Function?
if i have sheets with data listed like this:-
C E G I K M O Pressure Names of Officers Pay Signature of Hours (see note 1) Total listed for overtime number Officer Cause From To H:M P11 SURNAME. 1 4534 05:00 06:00 01:00 B13 SURNAME. 1 4534 06:30 07:00 00:30 B15 SURNAME. 2 2475 07:00 07:30 00:30 B16 SURNAME. 2 2475 07:30 08:00 00:30 G11 SURNAME. 3 9341 08:00 08:30 00:30 B33 SURNAME. 4 12331 08:30 09:00 00:30 B17 SURNAME. 5 23211 09:00 09:30 00:30 G12 SURNAME. 8 2099 09:30 10:00 00:30 M34 SURNAME. 12 334 10:00 10:30 00:30 M42 SURNAME. 16 54367 10:30 11:00 00:30 M43 SURNAME. 6 12333 11:00 11:30 00:30 what function could i use to look up this data from another sheet, to generate a list showing total time per day for each member of staff, and if you click on each staff name it will expand to show a break down? something like this :- Before expanding Day Pay Number Name Total H:M Monday 2475 SURNAME. 57 10:30 after Monday Pay Number Name Cause From To Total H:M Monday 2475 SURNAME. 2 P11 5:00:00 AM 06:00:00 AM 1:00 Monday 2475 SURNAME. 2 B13 6:30:00 AM 07:00:00 AM 0:30 Monday 2475 SURNAME. 2 B15 7:00:00 AM 07:30:00 AM 0:30 Monday 2475 SURNAME. 2 B16 7:30:00 AM 08:00:00 AM 0:30 Monday 2475 SURNAME. 2 G11 8:00:00 AM 08:30:00 AM 0:30 Monday 2475 SURNAME. 2 B33 8:30:00 AM 09:00:00 AM 0:30 Monday 2475 SURNAME. 2 B17 9:00:00 AM 09:30:00 AM 0:30 Monday 2475 SURNAME. 2 G12 9:30:00 AM 10:00:00 AM 0:30 Monday 2475 SURNAME. 2 M34 10:00:00 AM 10:30:00 AM 0:30 Monday 2475 SURNAME. 2 M42 10:30:00 AM 11:00:00 AM 0:30 Monday 2475 SURNAME. 2 M43 11:00:00 AM 11:30:00 AM 0:30 Monday 2475 SURNAME. 2 M51 11:30:00 AM 12:00:00 PM 0:30 Monday 2475 SURNAME. 2 N31 12:00:00 PM 12:30:00 PM 0:30 Monday 2475 SURNAME. 2 N32 12:30:00 PM 01:00:00 PM 0:30 Monday 2475 SURNAME. 2 N33 1:00:00 PM 01:30:00 PM 0:30 Monday 2475 SURNAME. 2 N35 1:30:00 PM 02:00:00 PM 0:30 Monday 2475 SURNAME. 2 R13 1:30:00 PM 02:00:00 PM 0:30 Monday 2475 SURNAME. 2 Sick Leave 1:30 Monday 2475 SURNAME. 57 10:30 If it would help to see the file for yourself, email me, steve at gamesaddict dot net Many thanks, Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
Help please, what Function?
sorry formatting was lost after send :(
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help please, what Function?
Hi
have a look at pivot tables. see http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -----Original Message----- if i have sheets with data listed like this:- C E G I K M O Pressure Names of Officers Pay Signature of Hours (see note 1) Total listed for overtime number Officer Cause From To H:M P11 SURNAME. 1 4534 05:00 06:00 01:00 B13 SURNAME. 1 4534 06:30 07:00 00:30 B15 SURNAME. 2 2475 07:00 07:30 00:30 B16 SURNAME. 2 2475 07:30 08:00 00:30 G11 SURNAME. 3 9341 08:00 08:30 00:30 B33 SURNAME. 4 12331 08:30 09:00 00:30 B17 SURNAME. 5 23211 09:00 09:30 00:30 G12 SURNAME. 8 2099 09:30 10:00 00:30 M34 SURNAME. 12 334 10:00 10:30 00:30 M42 SURNAME. 16 54367 10:30 11:00 00:30 M43 SURNAME. 6 12333 11:00 11:30 00:30 what function could i use to look up this data from another sheet, to generate a list showing total time per day for each member of staff, and if you click on each staff name it will expand to show a break down? something like this :- Before expanding Day Pay Number Name Total H:M Monday 2475 SURNAME. 57 10:30 after Monday Pay Number Name Cause From To Total H:M Monday 2475 SURNAME. 2 P11 5:00:00 AM 06:00:00 AM 1:00 Monday 2475 SURNAME. 2 B13 6:30:00 AM 07:00:00 AM 0:30 Monday 2475 SURNAME. 2 B15 7:00:00 AM 07:30:00 AM 0:30 Monday 2475 SURNAME. 2 B16 7:30:00 AM 08:00:00 AM 0:30 Monday 2475 SURNAME. 2 G11 8:00:00 AM 08:30:00 AM 0:30 Monday 2475 SURNAME. 2 B33 8:30:00 AM 09:00:00 AM 0:30 Monday 2475 SURNAME. 2 B17 9:00:00 AM 09:30:00 AM 0:30 Monday 2475 SURNAME. 2 G12 9:30:00 AM 10:00:00 AM 0:30 Monday 2475 SURNAME. 2 M34 10:00:00 AM 10:30:00 AM 0:30 Monday 2475 SURNAME. 2 M42 10:30:00 AM 11:00:00 AM 0:30 Monday 2475 SURNAME. 2 M43 11:00:00 AM 11:30:00 AM 0:30 Monday 2475 SURNAME. 2 M51 11:30:00 AM 12:00:00 PM 0:30 Monday 2475 SURNAME. 2 N31 12:00:00 PM 12:30:00 PM 0:30 Monday 2475 SURNAME. 2 N32 12:30:00 PM 01:00:00 PM 0:30 Monday 2475 SURNAME. 2 N33 1:00:00 PM 01:30:00 PM 0:30 Monday 2475 SURNAME. 2 N35 1:30:00 PM 02:00:00 PM 0:30 Monday 2475 SURNAME. 2 R13 1:30:00 PM 02:00:00 PM 0:30 Monday 2475 SURNAME. 2 Sick Leave 1:30 Monday 2475 SURNAME. 57 10:30 If it would help to see the file for yourself, email me, steve at gamesaddict dot net Many thanks, Steve . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help please, what Function?
i need a function that will auto update the looked up data if its changed
Thanks, Steve "Frank Kabel" wrote in message ... Hi have a look at pivot tables. see http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -----Original Message----- if i have sheets with data listed like this:- C E G I K M O Pressure Names of Officers Pay Signature of Hours (see note 1) Total listed for overtime number Officer Cause From To H:M P11 SURNAME. 1 4534 05:00 06:00 01:00 B13 SURNAME. 1 4534 06:30 07:00 00:30 B15 SURNAME. 2 2475 07:00 07:30 00:30 B16 SURNAME. 2 2475 07:30 08:00 00:30 G11 SURNAME. 3 9341 08:00 08:30 00:30 B33 SURNAME. 4 12331 08:30 09:00 00:30 B17 SURNAME. 5 23211 09:00 09:30 00:30 G12 SURNAME. 8 2099 09:30 10:00 00:30 M34 SURNAME. 12 334 10:00 10:30 00:30 M42 SURNAME. 16 54367 10:30 11:00 00:30 M43 SURNAME. 6 12333 11:00 11:30 00:30 what function could i use to look up this data from another sheet, to generate a list showing total time per day for each member of staff, and if you click on each staff name it will expand to show a break down? something like this :- Before expanding Day Pay Number Name Total H:M Monday 2475 SURNAME. 57 10:30 after Monday Pay Number Name Cause From To Total H:M Monday 2475 SURNAME. 2 P11 5:00:00 AM 06:00:00 AM 1:00 Monday 2475 SURNAME. 2 B13 6:30:00 AM 07:00:00 AM 0:30 Monday 2475 SURNAME. 2 B15 7:00:00 AM 07:30:00 AM 0:30 Monday 2475 SURNAME. 2 B16 7:30:00 AM 08:00:00 AM 0:30 Monday 2475 SURNAME. 2 G11 8:00:00 AM 08:30:00 AM 0:30 Monday 2475 SURNAME. 2 B33 8:30:00 AM 09:00:00 AM 0:30 Monday 2475 SURNAME. 2 B17 9:00:00 AM 09:30:00 AM 0:30 Monday 2475 SURNAME. 2 G12 9:30:00 AM 10:00:00 AM 0:30 Monday 2475 SURNAME. 2 M34 10:00:00 AM 10:30:00 AM 0:30 Monday 2475 SURNAME. 2 M42 10:30:00 AM 11:00:00 AM 0:30 Monday 2475 SURNAME. 2 M43 11:00:00 AM 11:30:00 AM 0:30 Monday 2475 SURNAME. 2 M51 11:30:00 AM 12:00:00 PM 0:30 Monday 2475 SURNAME. 2 N31 12:00:00 PM 12:30:00 PM 0:30 Monday 2475 SURNAME. 2 N32 12:30:00 PM 01:00:00 PM 0:30 Monday 2475 SURNAME. 2 N33 1:00:00 PM 01:30:00 PM 0:30 Monday 2475 SURNAME. 2 N35 1:30:00 PM 02:00:00 PM 0:30 Monday 2475 SURNAME. 2 R13 1:30:00 PM 02:00:00 PM 0:30 Monday 2475 SURNAME. 2 Sick Leave 1:30 Monday 2475 SURNAME. 57 10:30 If it would help to see the file for yourself, email me, steve at gamesaddict dot net Many thanks, Steve . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help please, what Function?
Hi
pivot tables will update: - you could either do this manually by clicking on the menu command - you can set-up a refesh interval - etc. IMHO based on your data structure and your other requirements (breakdowns, etc.) are the only thing to go. Just test them -----Original Message----- i need a function that will auto update the looked up data if its changed Thanks, Steve "Frank Kabel" wrote in message ... Hi have a look at pivot tables. see http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -----Original Message----- if i have sheets with data listed like this:- C E G I K M O Pressure Names of Officers Pay Signature of Hours (see note 1) Total listed for overtime number Officer Cause From To H:M P11 SURNAME. 1 4534 05:00 06:00 01:00 B13 SURNAME. 1 4534 06:30 07:00 00:30 B15 SURNAME. 2 2475 07:00 07:30 00:30 B16 SURNAME. 2 2475 07:30 08:00 00:30 G11 SURNAME. 3 9341 08:00 08:30 00:30 B33 SURNAME. 4 12331 08:30 09:00 00:30 B17 SURNAME. 5 23211 09:00 09:30 00:30 G12 SURNAME. 8 2099 09:30 10:00 00:30 M34 SURNAME. 12 334 10:00 10:30 00:30 M42 SURNAME. 16 54367 10:30 11:00 00:30 M43 SURNAME. 6 12333 11:00 11:30 00:30 what function could i use to look up this data from another sheet, to generate a list showing total time per day for each member of staff, and if you click on each staff name it will expand to show a break down? something like this :- Before expanding Day Pay Number Name Total H:M Monday 2475 SURNAME. 57 10:30 after Monday Pay Number Name Cause From To Total H:M Monday 2475 SURNAME. 2 P11 5:00:00 AM 06:00:00 AM 1:00 Monday 2475 SURNAME. 2 B13 6:30:00 AM 07:00:00 AM 0:30 Monday 2475 SURNAME. 2 B15 7:00:00 AM 07:30:00 AM 0:30 Monday 2475 SURNAME. 2 B16 7:30:00 AM 08:00:00 AM 0:30 Monday 2475 SURNAME. 2 G11 8:00:00 AM 08:30:00 AM 0:30 Monday 2475 SURNAME. 2 B33 8:30:00 AM 09:00:00 AM 0:30 Monday 2475 SURNAME. 2 B17 9:00:00 AM 09:30:00 AM 0:30 Monday 2475 SURNAME. 2 G12 9:30:00 AM 10:00:00 AM 0:30 Monday 2475 SURNAME. 2 M34 10:00:00 AM 10:30:00 AM 0:30 Monday 2475 SURNAME. 2 M42 10:30:00 AM 11:00:00 AM 0:30 Monday 2475 SURNAME. 2 M43 11:00:00 AM 11:30:00 AM 0:30 Monday 2475 SURNAME. 2 M51 11:30:00 AM 12:00:00 PM 0:30 Monday 2475 SURNAME. 2 N31 12:00:00 PM 12:30:00 PM 0:30 Monday 2475 SURNAME. 2 N32 12:30:00 PM 01:00:00 PM 0:30 Monday 2475 SURNAME. 2 N33 1:00:00 PM 01:30:00 PM 0:30 Monday 2475 SURNAME. 2 N35 1:30:00 PM 02:00:00 PM 0:30 Monday 2475 SURNAME. 2 R13 1:30:00 PM 02:00:00 PM 0:30 Monday 2475 SURNAME. 2 Sick Leave 1:30 Monday 2475 SURNAME. 57 10:30 If it would help to see the file for yourself, email me, steve at gamesaddict dot net Many thanks, Steve . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help please, what Function?
Frank could you take a look and get me started please?
Many thanks, Steve |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help please, what Function?
Hi#you could mail me your file (please don't attach a file
to this NG). But it will be probably late in this evening or tomorrow) -----Original Message----- Frank could you take a look and get me started please? Many thanks, Steve . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
| User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming | |||
Linear Mode
