Cumulative/This period Totals

[jswarb001]

HI my payroll system produces a spreadsheet showing cumulative weekly figures of different types of pay in each column along the top , and a row for each Department along the side. There are 4 weeks in each period but as the figures are cumulative I need to know what each weeks figures are for each Dept. So at present I have to merge say period 4 figures alongside period 3 figures and then enter a formula to deduct one from the other to get "THIS PERIOD" only.

Its a bit time consuming and there are over 20 columns as well as 200 rows. Is there an easier way to do this (eg Macro?) without having to have to copy/paste/insert formulas etc each week?

thanks

[DonkeyOte]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Obviously given the nature of your job / file in question please dummy names & values as appropriate before uploading anything

[jswarb001]

Ok thanks So I have last weeks spreadsheet (in tab called period 3) and this weeks spreadsheet in tab called period 4. I want to as easily as possible each week subtract all the figures in each row and column in period 3 to the equivalent rows and columns in period 4. As the figures are cumulative it would then give me period 4 figures for that period only (ie as non cumulative)

The same exercise would then continue week after week.

thanks for any help

[jswarb001]

Hi could anyone help with this please?

thanks

[DonkeyOte]

It might be an idea to post a file which also outlines where exactly you intend for the results to be displayed ?

In more general terms... the most obvious solution is to use the INDIRECT function, however, it is vital that you are aware that this function is Volatile meaning it brings with it calculation overheads (see link in my sig. for more info on the subject)... so pending the amount of calculations & sheets in your file it may well be the case that this approach will prove ill-advised long-term.

Other possibilities would be to simply copy formulae from prior week sheet to latest sheet (as and when added to file) and subsequently run an Edit -> Replace so as to update the old links to the appropriate sheet thereby avoiding issues re: Volatile functions.

So I guess the key questions relate to how this file is maintained - ie:

-- when are new sheets are added ?

-- how is that process controlled ? (for ex. are there strict naming conventions in place etc ?)

-- what is the (likely) maximum amount of data stored in this file ?

One final possibility might be to use Laurent Longre's morefunc.xll add-in which comes with the useful SHEETOFFSET function, however, you will need to establish as to whether or not you are able to install 3rd party add-ins before pursuing that route further.