ExcelBanter - Which one to use:Macros/formula/Lookup

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Which one to use:Macros/formula/Lookup (https://www.excelbanter.com/excel-discussion-misc-queries/229448-one-use-macros-formula-lookup.html)

Which one to use:Macros/formula/Lookup

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

--
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=92082

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

Threads merged :)

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

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

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' (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

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

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.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.

"Simon Lloyd" wrote:

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' (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

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

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

"JBeaucaire" wrote:

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.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.

"Simon Lloyd" wrote:

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' (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

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

[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" wrote:

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' (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

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

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 Wrote:
[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" wrote:

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
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'which one to use: Macros / Formula's / Lookup -
The Code Cage Forums'
(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

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

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" wrote:

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 Wrote:
[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" wrote:

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
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'which one to use: Macros / Formula's / Lookup -
The Code Cage Forums'
(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