Which one to use:Macros/formula/Lookup

A

Aussiegirlone

Hello Simon

I finally got here so I hope you are still able to help Bg:)

original message:

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?

attachment has been provided in case you are able to help.

PS: Thankyou for helping me so far :)
Regards
aussiegirlone


+-------------------------------------------------------------------+
|Filename: Example Employees Roster.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=116|
+-------------------------------------------------------------------+
 
S

Simon Lloyd

Aussiegirlone;329380 said:
Hello Simon

I finally got here so I hope you are still able to help Bg:)

original message:

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?

attachment has been provided in case you are able to help.

PS: Thankyou for helping me so far :)
Regards
aussiegirlone
Click to expand...
Aussiegirlone, having looked at your workbook structure you need to
clarify some things....in the employee sheet you have a column for Date
but in your patrol sites etc. you dont supply a date, finding and moving
data would be easier if the employees were prefixed with a date (those
without wouldn't be transferred) it could be that you may need to change
your structure a little.

Do you want to run the collection daily, weekly or monthly?


--
Simon Lloyd

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

JBeaucaire

While Simon is working out the macro process, just a tip on layout
preparation: RESIST the urge to format columns down the entire worksheet.

On your posted sample, I went down to row 100 on the two employee sheets,
highlighted ALL the rows from 100 down and selected EDIT > CLEAR > ALL.
After doing that to the two sheets, and saving again, the size of your
workbook dropped from almost 5 MEGS to 70Kb.

You can add formatting as you go, or have your macro do that.
 
A

Aussiegirlone

Thanks Simon for looking at the workbook; the Date is something I did
over-look but has been corrected. As for the formatting the entire
sheet, it is a mistake I do almost everytime; and often I end up having
to start over just because of that.
Thankyou for the advice, you've been a great help and I look forward to
the macro
aussiegirlone
 
A

aussiegirlone

Thanks Simon for looking at the workbook; the Date is something I did
over-look but has been corrected. As for the formatting the entire sheet, it
is a mistake I do almost everytime; and often I end up having to start over
just because of that.
Thankyou for the advice, you've been a great help and I look forward to the
macro
regards
aussiegirlone
 
A

aussiegirlone

[Do I want to run the collection daily, weekly or monthly?]
Daily in case changes need to be made on any employee's roster
 
S

Simon Lloyd

Aussiegirlone, do you want to post your revised workbook in the forum
with the changes showing where you have put the dates etc., you also
need to address some issues around times, 12:00:00 is not the same as
12:00:00 AM when working with times, you need to choose to either show
AM or PM or not, it will make life easier for you in future.

I also need a clearer view of what you want, looking at your workbook
you want to collect data from each patrol sheet and show it on the
employee sheet but there is no way to determine the last entry/change
i.e if we are on week 5 and you change week 2, Wednesday for Employee 14
without collecting all the data and writing over the entire employee
sheet with the updated data i can't see a way of capturing it. Do you
have any ideas or would you like me to knock something together that you
can play around with and see if it suits your needs?

aussiegirlone;329496 said:
[Do I want to run the collection daily, weekly or monthly?]
Daily in case changes need to be made on any employee's roster

Simon Lloyd said:
Aussiegirlone, having looked at your workbook structure you need to
clarify some things....in the employee sheet you have a column for Date
but in your patrol sites etc. you dont supply a date, finding and moving
data would be easier if the employees were prefixed with a date (those
without wouldn't be transferred) it could be that you may need to change
your structure a little.

Do you want to run the collection daily, weekly or monthly?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage' (http://www.thecodecage.com))
------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Click to expand...
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'which one to use: Macros / Formula's / Lookup -
Click to expand...
The Code Cage Forums'
(http://www.thecodecage.com/forumz/showthread.php?t=91647)
Click to expand...


--
Simon Lloyd

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

aussiegirlone

Revised Roster supplied
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
Thankyou so very much
Aussiegirlone


Simon Lloyd said:
Aussiegirlone, do you want to post your revised workbook in the forum
with the changes showing where you have put the dates etc., you also
need to address some issues around times, 12:00:00 is not the same as
12:00:00 AM when working with times, you need to choose to either show
AM or PM or not, it will make life easier for you in future.

I also need a clearer view of what you want, looking at your workbook
you want to collect data from each patrol sheet and show it on the
employee sheet but there is no way to determine the last entry/change
i.e if we are on week 5 and you change week 2, Wednesday for Employee 14
without collecting all the data and writing over the entire employee
sheet with the updated data i can't see a way of capturing it. Do you
have any ideas or would you like me to knock something together that you
can play around with and see if it suits your needs?

aussiegirlone;329496 said:
[Do I want to run the collection daily, weekly or monthly?]
Daily in case changes need to be made on any employee's roster

Simon Lloyd said:
Aussiegirlone;329380 Wrote:
Hello Simon

I finally got here so I hope you are still able to help Bg:)

original message:

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?

attachment has been provided in case you are able to help.

PS: Thankyou for helping me so far :)
Regards
aussiegirlone
Aussiegirlone, having looked at your workbook structure you need to
clarify some things....in the employee sheet you have a column for Date
but in your patrol sites etc. you dont supply a date, finding and moving
data would be easier if the employees were prefixed with a date (those
without wouldn't be transferred) it could be that you may need to change
your structure a little.

Do you want to run the collection daily, weekly or monthly?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage' (http://www.thecodecage.com))
------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Click to expand...
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'which one to use: Macros / Formula's / Lookup -
Click to expand...
The Code Cage Forums'
(http://www.thecodecage.com/forumz/showthread.php?t=91647)
Click to expand...


--
Simon Lloyd

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

which one to use: Macros / Formula's / Lookup 10
Display Ticket Numbers in Other sheet based on condion 1
Linking data from one worksheet to another using ookup functions 2
Text into Numeric values? 4
how do i use v lookup 0
Transferring data 17
Repeat copy for indifinate number of worksheets 3
Lookup macro 3

Top