Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
which one to use: Macros / Formula's / Lookup
I have created an Employees Roster have got 15 sheets each Titled as a Site
Location. Then I have got another 15 sheets, each with the name of a single employee. What I would like to do is have the employees Roster Sheets automatically search all the Site Sheets for the data entered. Thus, what ever is entered into the site sheets it automatically enters the data into the corresponding employee. If this is possible, can anyone help with a formula or macro to do this? Aussiegirlone Data entered here Sheet Name: ParkPatrols Name Start Finish Sun Mon Tue Wed Thu Fri Sat Total Hours ???? 12:00 pm 16:00 pm yes 4 Goes automatically here Sheet Name: Employee (1) ???? Lic Number 1234567 Day Date Start Time End Time Site Total Hours |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
which one to use: Macros / Formula's / Lookup
We would need more information and possibly see a workbook, however, you can do what you need with VBA to look over the 15 sheets for each employee and add the data to their sheet, you will also probably need to determine which data should be picked up by date. aussiegirlone;328002 Wrote: I have created an Employees Roster have got 15 sheets each Titled as a Site Location. Then I have got another 15 sheets, each with the name of a single employee. What I would like to do is have the employees Roster Sheets automatically search all the Site Sheets for the data entered. Thus, what ever is entered into the site sheets it automatically enters the data into the corresponding employee. If this is possible, can anyone help with a formula or macro to do this? Aussiegirlone Data entered here Sheet Name: ParkPatrols Name Start Finish Sun Mon Tue Wed Thu Fri Sat Total Hours ???? 12:00 pm 16:00 pm yes 4 Goes automatically here Sheet Name: Employee (1) ???? Lic Number 1234567 Day Date Start Time End Time Site Total Hours -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91647 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
which one to use: Macros / Formula's / Lookup
I'll give you the information that you want, if you tell me what information
you want. Can you give me an example of your suggestion please? "aussiegirlone" wrote: I have created an Employees Roster have got 15 sheets each Titled as a Site Location. Then I have got another 15 sheets, each with the name of a single employee. What I would like to do is have the employees Roster Sheets automatically search all the Site Sheets for the data entered. Thus, what ever is entered into the site sheets it automatically enters the data into the corresponding employee. If this is possible, can anyone help with a formula or macro to do this? Aussiegirlone Data entered here Sheet Name: ParkPatrols Name Start Finish Sun Mon Tue Wed Thu Fri Sat Total Hours ???? 12:00 pm 16:00 pm yes 4 Goes automatically here Sheet Name: Employee (1) ???? Lic Number 1234567 Day Date Start Time End Time Site Total Hours |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
which one to use: Macros / Formula's / Lookup
The data in the site sheets for each employee - are you looking to get all the data for that employee or just for a certain date or the last entry?, if you get all the data then the next time you run the coe it will again collect all the data, the only way to ensure not getting duplicates is to overwrite all the data in the employee sheet which may be counter productive not to mention time and memory consuming! There are many ways to do it here's just one, change/add the sheet names in Arr for your site sheets, only run the code when the activesheet is the employee sheet (and assuming the sheet name is that of the employee exactly as it will be found in the site sheets) Sub Find_employee_data() Dim Sh As Worksheet Dim Arr As Variant Dim MySheet As String Dim rng As Range, MyCell As Range Application.ScreenUpdating = False Arr = Sheets(Array("Sheet1", "Sheet2", "Sheet3")) MySheet = ActiveSheet.Name For Each Sh In Arr Set rng = Sh.Range("A1:A" & Sh.Range("A" & Rows.Count).End(xlUp).Row) For Each MyCell In rng If MyCell = MySheet Then MyCell.EntireRow.Copy Destination:=Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If Next MyCell Next Sh Application.ScreenUpdating = True End Sub aussiegirlone;328132 Wrote: I'll give you the information that you want, if you tell me what information you want. Can you give me an example of your suggestion please? "aussiegirlone" wrote: I have created an Employees Roster have got 15 sheets each Titled as a Site Location. Then I have got another 15 sheets, each with the name of a single employee. What I would like to do is have the employees Roster Sheets automatically search all the Site Sheets for the data entered. Thus, what ever is entered into the site sheets it automatically enters the data into the corresponding employee. If this is possible, can anyone help with a formula or macro to do this? Aussiegirlone Data entered here Sheet Name: ParkPatrols Name Start Finish Sun Mon Tue Wed Thu Fri Sat Total Hours ???? 12:00 pm 16:00 pm yes 4 Goes automatically here Sheet Name: Employee (1) ???? Lic Number 1234567 Day Date Start Time End Time Site Total Hours -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91647 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
which one to use: Macros / Formula's / Lookup
I think to understand you slightly! I need to be able to collect all the data
of each employee for the total week as the roster needs to be printed out and sent to each employee on a weekly basis. The data should be able to accumilate as the weeks go by without duplications. Thus, I should be able to see previous weeks of each employee's Data. Maybe if I send you two Site sheets and two employee sheets then you might see what I want a lot better? "Simon Lloyd" wrote: The data in the site sheets for each employee - are you looking to get all the data for that employee or just for a certain date or the last entry?, if you get all the data then the next time you run the coe it will again collect all the data, the only way to ensure not getting duplicates is to overwrite all the data in the employee sheet which may be counter productive not to mention time and memory consuming! There are many ways to do it here's just one, change/add the sheet names in Arr for your site sheets, only run the code when the activesheet is the employee sheet (and assuming the sheet name is that of the employee exactly as it will be found in the site sheets) Sub Find_employee_data() Dim Sh As Worksheet Dim Arr As Variant Dim MySheet As String Dim rng As Range, MyCell As Range Application.ScreenUpdating = False Arr = Sheets(Array("Sheet1", "Sheet2", "Sheet3")) MySheet = ActiveSheet.Name For Each Sh In Arr Set rng = Sh.Range("A1:A" & Sh.Range("A" & Rows.Count).End(xlUp).Row) For Each MyCell In rng If MyCell = MySheet Then MyCell.EntireRow.Copy Destination:=Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If Next MyCell Next Sh Application.ScreenUpdating = True End Sub aussiegirlone;328132 Wrote: I'll give you the information that you want, if you tell me what information you want. Can you give me an example of your suggestion please? "aussiegirlone" wrote: I have created an Employees Roster have got 15 sheets each Titled as a Site Location. Then I have got another 15 sheets, each with the name of a single employee. What I would like to do is have the employees Roster Sheets automatically search all the Site Sheets for the data entered. Thus, what ever is entered into the site sheets it automatically enters the data into the corresponding employee. If this is possible, can anyone help with a formula or macro to do this? Aussiegirlone Data entered here รข* Sheet Name: ParkPatrols Name Start Finish Sun Mon Tue Wed Thu Fri Sat Total Hours ???? 12:00 pm 16:00 pm yes 4 Goes automatically here รข* Sheet Name: Employee (1) ???? Lic Number 1234567 Day Date Start Time End Time Site Total Hours -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91647 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
which one to use: Macros / Formula's / Lookup
I think to understand you slightly! I need to be able to get all the data in
the site sheets for the total week of each employee. The roster is then to be sent to each employee on a weekly basis. The data collected should accumulate as the weeks go by without duplications. Maybe I should send you a copy of two site sheets and two employee sheets if that would help "Simon Lloyd" wrote: The data in the site sheets for each employee - are you looking to get all the data for that employee or just for a certain date or the last entry?, if you get all the data then the next time you run the coe it will again collect all the data, the only way to ensure not getting duplicates is to overwrite all the data in the employee sheet which may be counter productive not to mention time and memory consuming! There are many ways to do it here's just one, change/add the sheet names in Arr for your site sheets, only run the code when the activesheet is the employee sheet (and assuming the sheet name is that of the employee exactly as it will be found in the site sheets) Sub Find_employee_data() Dim Sh As Worksheet Dim Arr As Variant Dim MySheet As String Dim rng As Range, MyCell As Range Application.ScreenUpdating = False Arr = Sheets(Array("Sheet1", "Sheet2", "Sheet3")) MySheet = ActiveSheet.Name For Each Sh In Arr Set rng = Sh.Range("A1:A" & Sh.Range("A" & Rows.Count).End(xlUp).Row) For Each MyCell In rng If MyCell = MySheet Then MyCell.EntireRow.Copy Destination:=Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If Next MyCell Next Sh Application.ScreenUpdating = True End Sub aussiegirlone;328132 Wrote: I'll give you the information that you want, if you tell me what information you want. Can you give me an example of your suggestion please? "aussiegirlone" wrote: I have created an Employees Roster have got 15 sheets each Titled as a Site Location. Then I have got another 15 sheets, each with the name of a single employee. What I would like to do is have the employees Roster Sheets automatically search all the Site Sheets for the data entered. Thus, what ever is entered into the site sheets it automatically enters the data into the corresponding employee. If this is possible, can anyone help with a formula or macro to do this? Aussiegirlone Data entered here รข* Sheet Name: ParkPatrols Name Start Finish Sun Mon Tue Wed Thu Fri Sat Total Hours ???? 12:00 pm 16:00 pm yes 4 Goes automatically here รข* Sheet Name: Employee (1) ???? Lic Number 1234567 Day Date Start Time End Time Site Total Hours -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91647 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
which one to use: Macros / Formula's / Lookup
If further help with it why not join our forums (shown in the link below) it's completely free, if you do join you will have the opportunity to add attachmnets to your posts so you can add workbooks to better illustrate your problems and get help directly with them. Also if you do join please post in this thread (link found below) so that people who have been following or helping with this query can continue to do so. :) aussiegirlone;328236 Wrote: I think to understand you slightly! I need to be able to get all the data in the site sheets for the total week of each employee. The roster is then to be sent to each employee on a weekly basis. The data collected should accumulate as the weeks go by without duplications. Maybe I should send you a copy of two site sheets and two employee sheets if that would help "Simon Lloyd" wrote: The data in the site sheets for each employee - are you looking to get all the data for that employee or just for a certain date or the last entry?, if you get all the data then the next time you run the coe it will again collect all the data, the only way to ensure not getting duplicates is to overwrite all the data in the employee sheet which may be counter productive not to mention time and memory consuming! There are many ways to do it here's just one, change/add the sheet names in Arr for your site sheets, only run the code when the activesheet is the employee sheet (and assuming the sheet name is that of the employee exactly as it will be found in the site sheets) Sub Find_employee_data() Dim Sh As Worksheet Dim Arr As Variant Dim MySheet As String Dim rng As Range, MyCell As Range Application.ScreenUpdating = False Arr = Sheets(Array("Sheet1", "Sheet2", "Sheet3")) MySheet = ActiveSheet.Name For Each Sh In Arr Set rng = Sh.Range("A1:A" & Sh.Range("A" & Rows.Count).End(xlUp).Row) For Each MyCell In rng If MyCell = MySheet Then MyCell.EntireRow.Copy Destination:=Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If Next MyCell Next Sh Application.ScreenUpdating = True End Sub aussiegirlone;328132 Wrote: I'll give you the information that you want, if you tell me what information you want. Can you give me an example of your suggestion please? "aussiegirlone" wrote: I have created an Employees Roster have got 15 sheets each Titled as a Site Location. Then I have got another 15 sheets, each with the name of a single employee. What I would like to do is have the employees Roster Sheets automatically search all the Site Sheets for the data entered. Thus, what ever is entered into the site sheets it automatically enters the data into the corresponding employee. If this is possible, can anyone help with a formula or macro to do this? Aussiegirlone Data entered here รข* Sheet Name: ParkPatrols Name Start Finish Sun Mon Tue Wed Thu Fri Sat Total Hours ???? 12:00 pm 16:00 pm yes 4 Goes automatically here รข* Sheet Name: Employee (1) ???? Lic Number 1234567 Day Date Start Time End Time Site Total Hours -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('http://www.thecodecage.com' (http://www.thecodecage.com/)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'http://www.thecodecage.com/forumz/member.php?userid=1' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'http://www.thecodecage.com/forumz/showthread.php?t=91647' (http://www.thecodecage.com/forumz/sh...ad.php?t=91647) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91647 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
which one to use: Macros / Formula's / Lookup
I have now become a member of thecodecage.com so now I ask where do I put
your suggested code (in what sheet(s) (links found below) "Simon Lloyd" wrote: If further help with it why not join our forums (shown in the link below) it's completely free, if you do join you will have the opportunity to add attachmnets to your posts so you can add workbooks to better illustrate your problems and get help directly with them. Also if you do join please post in this thread (link found below) so that people who have been following or helping with this query can continue to do so. :) aussiegirlone;328236 Wrote: I think to understand you slightly! I need to be able to get all the data in the site sheets for the total week of each employee. The roster is then to be sent to each employee on a weekly basis. The data collected should accumulate as the weeks go by without duplications. Maybe I should send you a copy of two site sheets and two employee sheets if that would help "Simon Lloyd" wrote: The data in the site sheets for each employee - are you looking to get all the data for that employee or just for a certain date or the last entry?, if you get all the data then the next time you run the coe it will again collect all the data, the only way to ensure not getting duplicates is to overwrite all the data in the employee sheet which may be counter productive not to mention time and memory consuming! There are many ways to do it here's just one, change/add the sheet names in Arr for your site sheets, only run the code when the activesheet is the employee sheet (and assuming the sheet name is that of the employee exactly as it will be found in the site sheets) Sub Find_employee_data() Dim Sh As Worksheet Dim Arr As Variant Dim MySheet As String Dim rng As Range, MyCell As Range Application.ScreenUpdating = False Arr = Sheets(Array("Sheet1", "Sheet2", "Sheet3")) MySheet = ActiveSheet.Name For Each Sh In Arr Set rng = Sh.Range("A1:A" & Sh.Range("A" & Rows.Count).End(xlUp).Row) For Each MyCell In rng If MyCell = MySheet Then MyCell.EntireRow.Copy Destination:=Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If Next MyCell Next Sh Application.ScreenUpdating = True End Sub aussiegirlone;328132 Wrote: I'll give you the information that you want, if you tell me what information you want. Can you give me an example of your suggestion please? "aussiegirlone" wrote: I have created an Employees Roster have got 15 sheets each Titled as a Site Location. Then I have got another 15 sheets, each with the name of a single employee. What I would like to do is have the employees Roster Sheets automatically search all the Site Sheets for the data entered. Thus, what ever is entered into the site sheets it automatically enters the data into the corresponding employee. If this is possible, can anyone help with a formula or macro to do this? Aussiegirlone Data entered here รยขรขฌ*รขฌล Sheet Name: ParkPatrols Name Start Finish Sun Mon Tue Wed Thu Fri Sat Total Hours ???? 12:00 pm 16:00 pm yes 4 Goes automatically here รยขรขฌ*รขฌล Sheet Name: Employee (1) ???? Lic Number 1234567 Day Date Start Time End Time Site Total Hours -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('http://www.thecodecage.com' (http://www.thecodecage.com/)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'http://www.thecodecage.com/forumz/member.php?userid=1' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'http://www.thecodecage.com/forumz/showthread.php?t=91647' (http://www.thecodecage.com/forumz/sh...ad.php?t=91647) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91647 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
which one to use: Macros / Formula's / Lookup
It goes like this. The roster is made up for the entire day of each employee for the total week. However if one employee decides not to show up [with only an hours notice] then the roster has to be change so that someone can replace that employee as they are all on call at a moments notice. Your comment: No way to determine the last entry/change My reply Once the week has ended all data entered will not be changed for the week ended however, the week must end with all jobs completed. If the week has not ended then changes should be able to be made. As for the times there will be no AM or PM Also here is the revised Roster. Can you tell me how to delete the other roster please?:) +-------------------------------------------------------------------+ |Filename: Example Employees Roster.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=118| +-------------------------------------------------------------------+ -- Aussiegirlone ------------------------------------------------------------------------ Aussiegirlone's Profile: http://www.thecodecage.com/forumz/member.php?userid=272 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91647 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
which one to use: Macros / Formula's / Lookup
Aussiegirlone;329601 Wrote: It goes like this. The roster is made up for the entire day of each employee for the total week. However if one employee decides not to show up [with only an hours notice] then the roster has to be change so that someone can replace that employee as they are all on call at a moments notice. Your comment: No way to determine the last entry/change My reply Once the week has ended all data entered will not be changed for the week ended however, the week must end with all jobs completed. If the week has not ended then changes should be able to be made. As for the times there will be no AM or PM Also here is the revised Roster. Can you tell me how to delete the other roster please?:) Just looking at your revised workbook i see the SITE sheet is now dated in single days yet 7 days across the columns and you no longer are looking at "Week1", "Week2"...etc, can you tell me what its is you really want to see and do? -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91647 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
which one to use: Macros / Formula's / Lookup
Simon Lloyd;334344 Wrote: Just looking at your revised workbook i see the SITE sheet is now dated in single days yet 7 days across the columns and you no longer are looking at "Week1", "Week2"...etc, can you tell me what its is you really want to see and do? I just did some quick changes, your workbook is now only 3 sheets, the dropdown data sheet you can hide, on the roster sheet choose an employye from the dropdown in A2 and then a week form the dropdown in B2 and the rest of the sheet will populate, your patrols sheet now has 52 weeks on it and the patrols on the right can be changed by clicking them as there is more data validation there, i have also split the times up from 21:00:00 - 23:00:00 to seperate columns this makes it easier to calculate hours worked. Anyway, after all that this workbook won't give you the data or flexibility you need, you need to go back to the design stage, for example, you can only assign one patrol site in any one week for any one employee! The workbook should get you started in the right direction when you disect it and see how its working. +-------------------------------------------------------------------+ |Filename: Example Employees Roster.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=122| +-------------------------------------------------------------------+ -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91647 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup, vlookup and macros | Excel Discussion (Misc queries) | |||
formula's | Excel Discussion (Misc queries) | |||
Formula's | Excel Worksheet Functions | |||
IF formula's | Excel Discussion (Misc queries) | |||
Need Help w/Formula's | Excel Worksheet Functions |