Help on designing a workbook

**charliebeth** · 09-16-2011, 11:20 AM

I have a compliated situation I need to make a workbook for. I need to get some background out first.

We are a cleaning company. We clean holiday parks and our staff are paid by the unit. There are a range of different price bands depending on the unit, for ease lets call them small, medium and large. Occasionally there are refresh (half the price) and deep (double price) cleans. The cleaners work in teams and the unit pay they earn is split between that team.

That is the simple part.

The complex part is that we do not always have the same staff on site each day we are there. The teams are often made up of different people from one day to the next because of this. The teams are sometimes made up of different numbers of people.

I need a spreadsheet that will work out the pay of each person, taking into account the days they have and have not worked, what units they have worked in, and who they have worked with (and therefore split that days money with).

Its a big ask.... can anyone take it on?

**Richard Buttrey** · 09-16-2011, 11:30 AM

Hi,

Are you asking for help to design and produce the whole thing, or just looking for suggestions as to the techniques to use?

I ask since at the moment we don't know your level of Excel experience.

Regards

**charliebeth** · 09-16-2011, 11:51 AM

My levels are limited.... I can do simple things like COUNT and some IF, Datasorting, etc.

I could make these sheet do what I want, but only in a very long winded and mostly manual way. It needs to be pretty fast.

I've got my basics here in an attachement. I used to have it so that the price per unit autofilled to correspond with the unit type cell, but someone took it out and I've forgotten how I did it (not my fault, I have small kids and I'm expecting again. Memory is not my strong point at the moment due to extreme lack of sleep!)

**charliebeth** · 09-16-2011, 11:53 AM

Ideally I would like to do it myself so help with tech would be ideal. But the more I learn the more I see I don't know....

**Andrew-R** · 09-16-2011, 12:02 PM

How does the attached work for you?

The price sheet contains a simple grid to give a basic price for refresh, normal or deep cleans by job size.

The jobs sheet lists each job, multiplies the type of job by the number of days and then checks how many staff days have been put in on that job to get a day rate/staff member

The staff sheet lists all of the staff and, by day, which job they were assigned to. The pay column multiples their day rate for each job by the number of days they spent on that job.

Is that close to what you were looking for?

**charliebeth** · 09-22-2011, 07:52 AM

Hi again

Sorry its been so long to reply. I have been ill and only returned to work this afternoon.

I'll have a look at that example and come back to you today.

Thanks

**Andrew-R** · 09-22-2011, 07:58 AM

No problems - hope you're feeling better now.

**charliebeth** · 09-26-2011, 06:36 AM

I feel like I am being a massive paid in the rear here....

I have shown that demo to the people who manage the site, and it's not quite what they are after. They have given me a version of the spreadsheet they are using now (with only 2 days on instead of the usual 12-15). What they want is to be able to auto-populate the first page with the wages of each cleaner - if they worked - on the correct day.

Is this possible? The spreadsheet itself will no doubt give you an idea of what we ourselves are able to do with excel.