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