which one to use: Macros / Formula's / Lookup

[aussiegirlone]

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]

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.

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)

 

[aussiegirlone]

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?

 

[Simon Lloyd]

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

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?

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[aussiegirlone]

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?

 

[aussiegirlone]

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]

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.

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

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[aussiegirlone]

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)

 

[Aussiegirlone]

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|
+-------------------------------------------------------------------+

 

[Simon Lloyd]

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]

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)