keeping track of payments

Hi all, new to the forum and was looking for some problem solving suggestions

heres my problem

i am a builder and i want to make a spread sheet to keep track of what i have been paid and the labour costs i have paid out

so i am working on 20 houses that all have 7 stages of payment each, and 5 to 8 men working on them who receive a price for each stage, but are paid a portion of that price each week while they are working on that stage

what i am trying to do is set up a sheet to keep track of what has been paid and the balance remaining for each stage and what has been received

i have tried doing it a few different ways but im none to clever with spread sheets and what is causing me the problem is next to each payment made i need to put the date and the name of the person who was paid so it always seems to just look a cluttered mess

have any of you got any ideas of how i could simplify this and make it look neat and easy

or alternatively is there some other software that may suit this application better?

thanks

How about this?
20 worksheets, one for each house plus an employee worksheet which lists employee number and name
Each row represents a week and the payments for that week to each man working on the house. As each week progresses you just add a date into a new row.
Each column represnts an employee and the payments, you can use LOOKUPs to pull employees names across.
You could then have a summary weeksheet that accumulates the data on each of the 20 "House" sheets and an account Sheet that will total all payments, etc

I'm assuming the same group of men work on the same house throught each stage though it doesnt really matter if extra employess are called in to work on the job, it just means more columns of data will be used.

Example (note the sheet tabs at the bottom):

http://i169.photobucket.com/albums/u...ilderSheet.jpg

Your layout is fine, Pete. I added some named ranges to your pricing sheet to make it easy to read the formulas that refer to it. Check out the new formulas in columns E and G. It should do what you want with your existing layout.

E2:
=INDEX(Prices,MATCH($A2,Plots,0),MATCH($B2,Jobs,0))

G2:
=E2-SUMIF($D$2:D2,D2,$F$2:F2)

...both formulas just drag/copy down and they adjust themselves accordingly.

Ok, first, let's get rid of those errors, so we'll check to make sure you've started a new row before we list anything.

E2:
=IF(B2="","",INDEX(Prices,MATCH($A2,Plots,0),MATCH($B2,Jobs,0)))

G2:
=IF(E2="","",E2-SUMIF($B$2:B2,B2,$F$2:F2))

Sorry, I keyed the last formula on the name instead of the job, I fixed that here, it's now only summing payments made to the same job stage (column B).

In Payment E2 you used this formula:

=IF(B2="","",INDEX(labour,MATCH($A2,Plot,0),MATCH($B2,job,0)))

You're getting a #NAME error because "labour" isn't defined anywhere. It's not a good idea to use NAME RANGES that match actual sheet names, either. You have sheet named Labour, and one named Prices... so perhaps you should highlight all the prices in the Labour (range B3:G22) and name it COSTS...

=IF(B2="","",INDEX(Costs,MATCH($A2,Plot,0),MATCH($B2,job,0)))

If you understand:
=INDEX(ValuesToReturn,MATCH(rowref,RowValues,0),MATCH(colref,ColumnValues,0))

...you're almost home. Named ranges in an INDEX/MATCH formula just make it easier to read.

Compare that visual version above to the formula I gave and you'll see how the pieces fit together.

Awesome, I knew you could!

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]