spreadsheet design question

  • Thread starter Thread starter jeanette.rimmer
  • Start date Start date
J

jeanette.rimmer

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

| 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
 
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.
 
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
 
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.
 
jeanette.rimmer said:
...I'm new to newsgroups
Click to expand...

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
 
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.
 
Back
Top