Automatically Changing Cell Reference's when Pasting in Excel
 

Hi everyone,

I use an Excel 97 spreadsheet at work to track stats for a number of
employee's. I have a problem with it, and I was wanting some help.
First, the basic structure of the sheet is thus:

*Individual worksheets for each employee, which just (as the rows),
have the days of the week, and in a couple of columns, have the actual
stats for each day:

Col B Col C Col D Col E
John Smith Bill Jones
13/2/2006 100 50 200 120
14/2/2006 120 70 100 98
etc

*One 'control' (or summary) worksheet. This sheet just has the days of
the week on it. Under each day, there are 5 lines (one for each
employee), and the cell's for each line reference the raw data from
the individual employee worksheets:

13/2/2006 Column B Col C Col D
John Smith 100 50
Bill Jones 200 120
etc
14/2/2006
John Smith 120 70
Bill Jones 100 98
etc

Now, here is the problem. I create 5 days at a time on the control
sheet (ie just the working week). So, in this example, I would
currently have 13/2/2006, 14/2/2006, 15/2/2006, 16/2/2006, 17/2/2006.
What I do at the end of the week is to highlight the entire area of
the last date (17/2/2006), Edit-Copy and then Edit-Paste onto a new
blank line. I do this 4 more times (to create my new 5 day week),
change the dates to reflect the correct ones (in this example, all 5
new days would have 17/2/2006 on them), and then I update the cell
reference's manually. This updating of cell references is the
problem. As each date on the control sheet has basically 10 rows in
it, when I copy and paste to a new day, the cell reference's in the
pasted copy have been updated by 10. However, the cell reference's
(which refer to the individual worksheets), actually should only be
increasing by 1 (as each date on the individual sheets only uses 1
row, not 10 like on the control sheet).

What I basically have to do when I update the cell references, is
check what cell should be referenced, highlight the cells for each
date, and then Edit-Replace. What I basically want to find out, is
there a way in Excel, to force it to only increase each new cell
reference by 1, instead of the 10 it currently does (due to their
being 10 rows in each date)?
--

Regards,

Spartacus