Fruit pickers nightmare?

[GrahamLodge]

Hi to all, I'm writing a spreadsheet for a friend of mine, that works out the wages for 50 picker of cherries, ie. Kg picked, price per Kg, tax deductions etc. Then all the data is put onto paystubs and all good with the workings. Now I've been asked to calculate the total Gross, Tax and Net pay for the 50 pickers.

The season is only about 6 weeks, and I have the Gross, Tax and Net pay in columns (AP2, AS2 & AV2 for the 50 workers), but making a separate "Totals sheet" with just this data on, is giving me a headache. Could anyone help me with a way to transfer the cell data of Gross Tax and Net data to possibly another sheet and tally them for the duration of the season, as the columns I have now are altering several time a day.

To make things a tad harder, pickers may leave mid week? so at anytime the user of the spreadsheets needs to know the totals of the above. If I could get the answer to one column, say, Net Pay, I'm sure I should be able to transfer that to the others, sorry for not being too clear, but I'll try answer any question? Many Thanks in advance.

 

[Latchmaker]

Does each Picker have a unique ID# such as an Associate# and if so what is the column for that associate? You can use a SUMIF formula to pull the totals to a seperate cell it is a simple formula. =sumif(Range of ID#'s,ID#that you are totaling(reference a cell and then just update that cell for quick update),Range of Cells you want to Total)

 

[GrahamLodge]

Hello and thanks for the reply Latchmaker, and yes to your question, B2 is the employees ID, then C2 with the names etc. I have down the left had side, 50 workers and across the top I have many columns of data ie. kg, price/per Kg, days of the week etc, with a column giving the gross pay that is being updated continually throughout the day from the Kg picked and the price per Kg which may vary during the day. At the end of the week, they can just print off the paystubs. At the moment, I can't see how the =sumif(range would work (please advise), but because of the amount of data that might accru with 50 workers over an approx. 45 day season, my original idea was to take the data off to (possibly) 3 other worksheets, say TotalsGross. TotalsTax and TotalsNet? Again I have tried this, but the figures keep updating in the new sheets, so I'm thinking of somehow? transferring say the Gross Pay column to the worksheet (TotalsGross) and placing it in column A then once a day do the same procedure but place it in column B, and have a column at the end for the totals? but I don't want the new A, B and C columns to be updating from the original cell in 'PayrollCalc' AP2: AP51. Sometimes I feel I can't see the wood for the trees... am I getting too complicated? and if it would work, how do I copy a column to another sheet and on Monday putting it in Column A of the new sheet and Tueday putting it in column B etc, automatically on startup and no other time throughout the day.

Your way looks easier, but not 100% sure how the data will look? and the 'PayrollCalc' sheet is quite a large sheet as it is, but I thank you for helping me, and I'm open to any suggestions.

Graham