Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
No I'm Spartacus
 
Posts: n/a
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I delete the automatically inserted ' in every Excel cell? SLW Excel Discussion (Misc queries) 6 August 24th 05 03:17 PM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
Changing Cell formats to date fields automatically PCLIVE Excel Worksheet Functions 3 April 12th 05 10:34 PM
Excel: How do I enter a date in one cell & automatically dates adj Shannon Excel Discussion (Misc queries) 4 January 12th 05 01:51 AM
Excel: how to formulate conditional cell references centraloffice Excel Worksheet Functions 1 December 10th 04 09:57 PM


All times are GMT +1. The time now is 09:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"