View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Velson
 
Posts: n/a
Default Copying a work sheet cell reference as relative not absolute?

Ron - thank you for the try. However:

1. The Findreplace does not find the text for the sheet name ('00-01') in
the sheet/cell reference.

2. Even if it had worked, I would have to repeat the exercise everytime I
copied the table which would reduce the efficiency of the copy. command.

3. There must be a standard practice in preparing accounts by month or year
for carrying balances forward. Just cannot see how to do it!

"Ron Coderre" wrote:

Try this:
If the new sheet refers to Sheet3, but you want it to refer to Sheet5....

Select the new sheet
EditReplace
Find what: Sheet3
Replace with: Sheet5
Click the [Replace All] button

Does that help? (if no...EditUndo)

***********
Regards,
Ron

XL2002, WinXP-Pro


"Velson" wrote:

Excel 2002, XP SP2
In preparing a sequence of annual reports on a standard template, I wish to
prepare a table that refers to the previous year's results, in the previous
sheet in a workbook. In copying the table to the next year, I want the sheet
reference to be relative to the working one, i.e. the previous sheet(year).
However when I copy the table including cell references to the succeeding
sheet, the original sheet reference remains the same.

One can declare cell references to be relative or absolute. How does one do
the same for sheet references?

The answer may be in the help files, but I cannot find it.