I am rewriting a series of spreadsheets and not 100% sure that Im going
about it the right way.

Im working in a recruitment agency that has contracts with 5 companies for
drivers.
Im planning on setting up a workbook for each company into which I will put
Time sheet information.

From these timesheets I will need to create a sheet which combines data by
driver as the drivers work on different contracts to produce a costing
sheet.
I was planning on filtering the time sheet data and pasting this onto the
costing sheet.

Does this sound like the best way forward?


Thanks

The way that I'd approach it is to put ALL the timesheet data into
one worksheet, and then extract the data that you need to run
reports, do calculations, make charts, etc. on other worksheets.
You just need to make sure that your timesheet data table includes
all the columns you need to allow you to select the records you want
for each task that you want to do - eg. Company ID, Driver ID, Job#,
Date, Time, Hours, Hourly Rate, # of widgets used, etc.
Then there's a large range of tools already in Excel to help you
present the data just about any way that you want to see it - Pivot
Tables, Charts, etc.

The key underlying concept is to put all your data in one table, then
do all your calculations, reports, charts someplace else.

Rgds,
ScottO

"jeanette.rimmer" wrote in message
...
| I am rewriting a series of spreadsheets and not 100% sure that Im
going
| about it the right way.
|
| Im working in a recruitment agency that has contracts with 5
companies for
| drivers.
| Im planning on setting up a workbook for each company into which I
will put
| Time sheet information.
|
| From these timesheets I will need to create a sheet which combines
data by
| driver as the drivers work on different contracts to produce a
costing
| sheet.
| I was planning on filtering the time sheet data and pasting this
onto the
| costing sheet.
|
| Does this sound like the best way forward?
|
|
| Thanks
|
|

Hi, Thanks Scott

I did think about doing it this way but my boss wasnt keen. He gets a bit
bamboozled by big spreadsheets!
I couldnt quite figure out in my head how I could do it as we get the info
from the different contracts in different ways but your email has helped.

Once I set up this data table, can I then filter by eg Company ID and copy
the data to another workbook?


Thanks

Jeanette


"ScottO" wrote in message
...
The way that I'd approach it is to put ALL the timesheet data into
one worksheet, and then extract the data that you need to run
reports, do calculations, make charts, etc. on other worksheets.
You just need to make sure that your timesheet data table includes
all the columns you need to allow you to select the records you want
for each task that you want to do - eg. Company ID, Driver ID, Job#,
Date, Time, Hours, Hourly Rate, # of widgets used, etc.
Then there's a large range of tools already in Excel to help you
present the data just about any way that you want to see it - Pivot
Tables, Charts, etc.

The key underlying concept is to put all your data in one table, then
do all your calculations, reports, charts someplace else.

Rgds,
ScottO

"jeanette.rimmer" wrote in message
...
| I am rewriting a series of spreadsheets and not 100% sure that Im
going
| about it the right way.
|
| Im working in a recruitment agency that has contracts with 5
companies for
| drivers.
| Im planning on setting up a workbook for each company into which I
will put
| Time sheet information.
|
| From these timesheets I will need to create a sheet which combines
data by
| driver as the drivers work on different contracts to produce a
costing
| sheet.
| I was planning on filtering the time sheet data and pasting this
onto the
| costing sheet.
|
| Does this sound like the best way forward?
|
|
| Thanks
|
|

Jeanette,

I disagree with the one big table approach.

I would have another sheet of static driver details, say driver name, any id
#, contracting company etc. Then on the timesheet sheet you would just use
the id or name and the times.

On the costing sheet, have the driver name in say A2, the start date in B2,
end date in C2, and then use formulae to get the aggregated data. For
instance

Contracting company might be =VLOOKUP(A2,Details!A1:H100,3,False)

where Details is the static data sheet, A1:H100 is the table and the
contracting company is in column C.

To get the total time for a period, if the timesheet sheet is called
Timesheet, and the name is in column A, date is in column B, hours worked in
column C, use

=SUMPRODUCT(--(Timesheet!$A$1:$A$1000=A2),--(Timesheet!$B$1:$B$1000=B2),--(
Timesheet!$C$1:$C$1000<=C2),$D$1:$D$1000)

Makes it easier to maintain.

--
HTH

Bob Phillips

"jeanette.rimmer" wrote in message
...
Hi, Thanks Scott

I did think about doing it this way but my boss wasnt keen. He gets a bit
bamboozled by big spreadsheets!
I couldnt quite figure out in my head how I could do it as we get the info
from the different contracts in different ways but your email has helped.

Once I set up this data table, can I then filter by eg Company ID and copy
the data to another workbook?


Thanks

Jeanette


"ScottO" wrote in message
...
The way that I'd approach it is to put ALL the timesheet data into
one worksheet, and then extract the data that you need to run
reports, do calculations, make charts, etc. on other worksheets.
You just need to make sure that your timesheet data table includes
all the columns you need to allow you to select the records you want
for each task that you want to do - eg. Company ID, Driver ID, Job#,
Date, Time, Hours, Hourly Rate, # of widgets used, etc.
Then there's a large range of tools already in Excel to help you
present the data just about any way that you want to see it - Pivot
Tables, Charts, etc.

The key underlying concept is to put all your data in one table, then
do all your calculations, reports, charts someplace else.

Rgds,
ScottO

"jeanette.rimmer" wrote in message
...
| I am rewriting a series of spreadsheets and not 100% sure that Im
going
| about it the right way.
|
| Im working in a recruitment agency that has contracts with 5
companies for
| drivers.
| Im planning on setting up a workbook for each company into which I
will put
| Time sheet information.
|
| From these timesheets I will need to create a sheet which combines
data by
| driver as the drivers work on different contracts to produce a
costing
| sheet.
| I was planning on filtering the time sheet data and pasting this
onto the
| costing sheet.
|
| Does this sound like the best way forward?
|
|
| Thanks
|
|

Thanks Bob

I have done what you suggested and have put driver details, pay rates etc on
a different worksheet and just have one with the timesheet data.

Im now trying to get data from this sheet into a separate workbook

The costing sheet is done by contracting company and needs to show all the
drivers that worked and their hours, Ive tried a vlookup using the
contracting company but Im only getting one record?


Any ideas, please let me know if I should post a new query somewhere else or
if here is ok, Im new to newsgroups


Jeanette

"Bob Phillips" wrote in message
...
Jeanette,

I disagree with the one big table approach.

I would have another sheet of static driver details, say driver name, any
id
#, contracting company etc. Then on the timesheet sheet you would just use
the id or name and the times.

On the costing sheet, have the driver name in say A2, the start date in
B2,
end date in C2, and then use formulae to get the aggregated data. For
instance

Contracting company might be =VLOOKUP(A2,Details!A1:H100,3,False)

where Details is the static data sheet, A1:H100 is the table and the
contracting company is in column C.

To get the total time for a period, if the timesheet sheet is called
Timesheet, and the name is in column A, date is in column B, hours worked
in
column C, use

=SUMPRODUCT(--(Timesheet!$A$1:$A$1000=A2),--(Timesheet!$B$1:$B$1000=B2),--(
Timesheet!$C$1:$C$1000<=C2),$D$1:$D$1000)

Makes it easier to maintain.

--
HTH

Bob Phillips

"jeanette.rimmer" wrote in message
...
Hi, Thanks Scott

I did think about doing it this way but my boss wasnt keen. He gets a bit
bamboozled by big spreadsheets!
I couldnt quite figure out in my head how I could do it as we get the
info
from the different contracts in different ways but your email has helped.

Once I set up this data table, can I then filter by eg Company ID and
copy
the data to another workbook?


Thanks

Jeanette


"ScottO" wrote in message
...
The way that I'd approach it is to put ALL the timesheet data into
one worksheet, and then extract the data that you need to run
reports, do calculations, make charts, etc. on other worksheets.
You just need to make sure that your timesheet data table includes
all the columns you need to allow you to select the records you want
for each task that you want to do - eg. Company ID, Driver ID, Job#,
Date, Time, Hours, Hourly Rate, # of widgets used, etc.
Then there's a large range of tools already in Excel to help you
present the data just about any way that you want to see it - Pivot
Tables, Charts, etc.

The key underlying concept is to put all your data in one table, then
do all your calculations, reports, charts someplace else.

Rgds,
ScottO

"jeanette.rimmer" wrote in message
...
| I am rewriting a series of spreadsheets and not 100% sure that Im
going
| about it the right way.
|
| Im working in a recruitment agency that has contracts with 5
companies for
| drivers.
| Im planning on setting up a workbook for each company into which I
will put
| Time sheet information.
|
| From these timesheets I will need to create a sheet which combines
data by
| driver as the drivers work on different contracts to produce a
costing
| sheet.
| I was planning on filtering the time sheet data and pasting this
onto the
| costing sheet.
|
| Does this sound like the best way forward?
|
|
| Thanks
|
|

Why are you trying to get it into a new workbook? That just makes it harder,
a new worksheet is good enough.

On the new worksheet, list all your drivers. Then use the formula for each
one.

Post back with your formulae.

--
HTH

Bob Phillips

"jeanette.rimmer" wrote in message
...
Thanks Bob

I have done what you suggested and have put driver details, pay rates etc
on
a different worksheet and just have one with the timesheet data.

Im now trying to get data from this sheet into a separate workbook

The costing sheet is done by contracting company and needs to show all the
drivers that worked and their hours, Ive tried a vlookup using the
contracting company but Im only getting one record?


Any ideas, please let me know if I should post a new query somewhere else
or
if here is ok, Im new to newsgroups


Jeanette

"Bob Phillips" wrote in message
...
Jeanette,

I disagree with the one big table approach.

I would have another sheet of static driver details, say driver name,
any
id
#, contracting company etc. Then on the timesheet sheet you would just
use
the id or name and the times.

On the costing sheet, have the driver name in say A2, the start date in
B2,
end date in C2, and then use formulae to get the aggregated data. For
instance

Contracting company might be =VLOOKUP(A2,Details!A1:H100,3,False)

where Details is the static data sheet, A1:H100 is the table and the
contracting company is in column C.

To get the total time for a period, if the timesheet sheet is called
Timesheet, and the name is in column A, date is in column B, hours
worked
in
column C, use


=SUMPRODUCT(--(Timesheet!$A$1:$A$1000=A2),--(Timesheet!$B$1:$B$1000=B2),--(
Timesheet!$C$1:$C$1000<=C2),$D$1:$D$1000)

Makes it easier to maintain.

--
HTH

Bob Phillips

"jeanette.rimmer" wrote in message
...
Hi, Thanks Scott

I did think about doing it this way but my boss wasnt keen. He gets a
bit
bamboozled by big spreadsheets!
I couldnt quite figure out in my head how I could do it as we get the
info
from the different contracts in different ways but your email has
helped.

Once I set up this data table, can I then filter by eg Company ID and
copy
the data to another workbook?


Thanks

Jeanette


"ScottO" wrote in message
...
The way that I'd approach it is to put ALL the timesheet data into
one worksheet, and then extract the data that you need to run
reports, do calculations, make charts, etc. on other worksheets.
You just need to make sure that your timesheet data table includes
all the columns you need to allow you to select the records you want
for each task that you want to do - eg. Company ID, Driver ID, Job#,
Date, Time, Hours, Hourly Rate, # of widgets used, etc.
Then there's a large range of tools already in Excel to help you
present the data just about any way that you want to see it - Pivot
Tables, Charts, etc.

The key underlying concept is to put all your data in one table, then
do all your calculations, reports, charts someplace else.

Rgds,
ScottO

"jeanette.rimmer" wrote in message
...
| I am rewriting a series of spreadsheets and not 100% sure that Im
going
| about it the right way.
|
| Im working in a recruitment agency that has contracts with 5
companies for
| drivers.
| Im planning on setting up a workbook for each company into which I
will put
| Time sheet information.
|
| From these timesheets I will need to create a sheet which combines
data by
| driver as the drivers work on different contracts to produce a
costing
| sheet.
| I was planning on filtering the time sheet data and pasting this
onto the
| costing sheet.
|
| Does this sound like the best way forward?
|
|
| Thanks
|
|

jeanette.rimmer wrote:

...I'm new to newsgroups

Jeanette. You're certainly in luck! This is the *only* newsgroup I have ever
seen where there is real and courteous discussion. Most of them are full of
profanities and useless pseudo "facts".

Kind regards to all!
Fred

Bob,

Im trying to create invoices from my original data which is why I thought
new workbooks would be a good idea but Ive taken your point.

I currently have a maste sheet with

Driver Contract Paying Agency Hrs at different pay rates



What Im trying to get in the invoice is all drivers from one Paying Agency
on the same sheet

Ive put a vlookup in A2, =VLOOKUP(C10,Sheet1!A1:I175,1) which should look
for the agency i entered in C10, on the second sheet, in the first sheet and
return the drivers name from column .

This is obviously only finding one name but I need to find all of them.

thanks in advance



"Bob Phillips" wrote in message
...
Why are you trying to get it into a new workbook? That just makes it
harder,
a new worksheet is good enough.

On the new worksheet, list all your drivers. Then use the formula for each
one.

Post back with your formulae.

--
HTH

Bob Phillips

"jeanette.rimmer" wrote in message
...
Thanks Bob

I have done what you suggested and have put driver details, pay rates etc
on
a different worksheet and just have one with the timesheet data.

Im now trying to get data from this sheet into a separate workbook

The costing sheet is done by contracting company and needs to show all
the
drivers that worked and their hours, Ive tried a vlookup using the
contracting company but Im only getting one record?


Any ideas, please let me know if I should post a new query somewhere else
or
if here is ok, Im new to newsgroups


Jeanette

"Bob Phillips" wrote in message
...
Jeanette,

I disagree with the one big table approach.

I would have another sheet of static driver details, say driver name,
any
id
#, contracting company etc. Then on the timesheet sheet you would just
use
the id or name and the times.

On the costing sheet, have the driver name in say A2, the start date in
B2,
end date in C2, and then use formulae to get the aggregated data. For
instance

Contracting company might be =VLOOKUP(A2,Details!A1:H100,3,False)

where Details is the static data sheet, A1:H100 is the table and the
contracting company is in column C.

To get the total time for a period, if the timesheet sheet is called
Timesheet, and the name is in column A, date is in column B, hours
worked
in
column C, use


=SUMPRODUCT(--(Timesheet!$A$1:$A$1000=A2),--(Timesheet!$B$1:$B$1000=B2),--(
Timesheet!$C$1:$C$1000<=C2),$D$1:$D$1000)

Makes it easier to maintain.

--
HTH

Bob Phillips

"jeanette.rimmer" wrote in message
...
Hi, Thanks Scott

I did think about doing it this way but my boss wasnt keen. He gets a
bit
bamboozled by big spreadsheets!
I couldnt quite figure out in my head how I could do it as we get the
info
from the different contracts in different ways but your email has
helped.

Once I set up this data table, can I then filter by eg Company ID and
copy
the data to another workbook?


Thanks

Jeanette


"ScottO" wrote in
message
...
The way that I'd approach it is to put ALL the timesheet data into
one worksheet, and then extract the data that you need to run
reports, do calculations, make charts, etc. on other worksheets.
You just need to make sure that your timesheet data table includes
all the columns you need to allow you to select the records you want
for each task that you want to do - eg. Company ID, Driver ID, Job#,
Date, Time, Hours, Hourly Rate, # of widgets used, etc.
Then there's a large range of tools already in Excel to help you
present the data just about any way that you want to see it - Pivot
Tables, Charts, etc.

The key underlying concept is to put all your data in one table,
then
do all your calculations, reports, charts someplace else.

Rgds,
ScottO

"jeanette.rimmer" wrote in message
...
| I am rewriting a series of spreadsheets and not 100% sure that Im
going
| about it the right way.
|
| Im working in a recruitment agency that has contracts with 5
companies for
| drivers.
| Im planning on setting up a workbook for each company into which I
will put
| Time sheet information.
|
| From these timesheets I will need to create a sheet which combines
data by
| driver as the drivers work on different contracts to produce a
costing
| sheet.
| I was planning on filtering the time sheet data and pasting this
onto the
| costing sheet.
|
| Does this sound like the best way forward?
|
|
| Thanks
|
|

If you use

=VLOOKUP(C10,Sheet1!$A$1:$I$175,3,False)

and it should get the agency. Copy A2 to A3, and it will lookup the value in
C11.

--
HTH

Bob Phillips

"jeanette.rimmer" wrote in message
...
Bob,

Im trying to create invoices from my original data which is why I thought
new workbooks would be a good idea but Ive taken your point.

I currently have a maste sheet with

Driver Contract Paying Agency Hrs at different pay rates



What Im trying to get in the invoice is all drivers from one Paying Agency
on the same sheet

Ive put a vlookup in A2, =VLOOKUP(C10,Sheet1!A1:I175,1) which should look
for the agency i entered in C10, on the second sheet, in the first sheet
and
return the drivers name from column .

This is obviously only finding one name but I need to find all of them.

thanks in advance



"Bob Phillips" wrote in message
...
Why are you trying to get it into a new workbook? That just makes it
harder,
a new worksheet is good enough.

On the new worksheet, list all your drivers. Then use the formula for
each
one.

Post back with your formulae.

--
HTH

Bob Phillips

"jeanette.rimmer" wrote in message
...
Thanks Bob

I have done what you suggested and have put driver details, pay rates
etc
on
a different worksheet and just have one with the timesheet data.

Im now trying to get data from this sheet into a separate workbook

The costing sheet is done by contracting company and needs to show all
the
drivers that worked and their hours, Ive tried a vlookup using the
contracting company but Im only getting one record?


Any ideas, please let me know if I should post a new query somewhere
else
or
if here is ok, Im new to newsgroups


Jeanette

"Bob Phillips" wrote in message
...
Jeanette,

I disagree with the one big table approach.

I would have another sheet of static driver details, say driver name,
any
id
#, contracting company etc. Then on the timesheet sheet you would
just
use
the id or name and the times.

On the costing sheet, have the driver name in say A2, the start date
in
B2,
end date in C2, and then use formulae to get the aggregated data. For
instance

Contracting company might be =VLOOKUP(A2,Details!A1:H100,3,False)

where Details is the static data sheet, A1:H100 is the table and the
contracting company is in column C.

To get the total time for a period, if the timesheet sheet is called
Timesheet, and the name is in column A, date is in column B, hours
worked
in
column C, use



=SUMPRODUCT(--(Timesheet!$A$1:$A$1000=A2),--(Timesheet!$B$1:$B$1000=B2),--(
Timesheet!$C$1:$C$1000<=C2),$D$1:$D$1000)

Makes it easier to maintain.

--
HTH

Bob Phillips

"jeanette.rimmer" wrote in message
...
Hi, Thanks Scott

I did think about doing it this way but my boss wasnt keen. He gets
a
bit
bamboozled by big spreadsheets!
I couldnt quite figure out in my head how I could do it as we get
the
info
from the different contracts in different ways but your email has
helped.

Once I set up this data table, can I then filter by eg Company ID
and
copy
the data to another workbook?


Thanks

Jeanette


"ScottO" wrote in
message
...
The way that I'd approach it is to put ALL the timesheet data into
one worksheet, and then extract the data that you need to run
reports, do calculations, make charts, etc. on other worksheets.
You just need to make sure that your timesheet data table includes
all the columns you need to allow you to select the records you
want
for each task that you want to do - eg. Company ID, Driver ID,
Job#,
Date, Time, Hours, Hourly Rate, # of widgets used, etc.
Then there's a large range of tools already in Excel to help you
present the data just about any way that you want to see it -
Pivot
Tables, Charts, etc.

The key underlying concept is to put all your data in one table,
then
do all your calculations, reports, charts someplace else.

Rgds,
ScottO

"jeanette.rimmer" wrote in message
...
| I am rewriting a series of spreadsheets and not 100% sure that
Im
going
| about it the right way.
|
| Im working in a recruitment agency that has contracts with 5
companies for
| drivers.
| Im planning on setting up a workbook for each company into which
I
will put
| Time sheet information.
|
| From these timesheets I will need to create a sheet which
combines
data by
| driver as the drivers work on different contracts to produce a
costing
| sheet.
| I was planning on filtering the time sheet data and pasting this
onto the
| costing sheet.
|
| Does this sound like the best way forward?
|
|
| Thanks
|
|