Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Velson
 
Posts: n/a
Default Copying a work sheet cell reference as relative not absolute?

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default Copying a work sheet cell reference as relative not absolute?

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.

  #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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default Copying a work sheet cell reference as relative not absolute?

A few comments....

In my test, Find/Replace found all references to sheet 00-01 and replaced
them with 02-03. If yours didn't, click the [options] button on the
Find/Replace dialog and make sure "Look in: Formulas" is selected. Also, make
sure you clidk the [Replace All] button (so you don't have to sit there all
day clicking the [Replace] button.

Assuming that you would only do that once per copy/paste, is that really an
oppressive amount of effort? Maybe Yes....I don't know your situation.

Next, Excel has no automatic relative sheet references (eg Sheet1-1). There
are workarounds, but I'm not sure if they fit your situation. If you have a
report sheet that you want to display data from other sheets in your workbbok
and those sheets are all structured identically, try this option:

Replace your direct references with indirect references.
For instance, if you want cell C5 on your report to refer to cell D10 on
some other sheet, change your formula
from this: ='00-01'!D10
to this: =INDIRECT(A1&"!D10")
Whatever sheet name you type in cell A1 will cause that formula to refer to
that sheet. If you enter 02-03 in cell A1, the formula in C5 will refer to
'02-03!D10.

There are other options, but let's start with that.

Does that help?

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

XL2002, WinXP-Pro


"Velson" wrote:

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.

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

Thank you for your help. This worked. The obvious solution is often the
best.

For this application, find/replace was not much effort, but it is not
"neat". Can we suggest to Microsoft to allow relative addressing for sheets?


"Ron Coderre" wrote:

A few comments....

In my test, Find/Replace found all references to sheet 00-01 and replaced
them with 02-03. If yours didn't, click the [options] button on the
Find/Replace dialog and make sure "Look in: Formulas" is selected. Also, make
sure you clidk the [Replace All] button (so you don't have to sit there all
day clicking the [Replace] button.

Assuming that you would only do that once per copy/paste, is that really an
oppressive amount of effort? Maybe Yes....I don't know your situation.

Next, Excel has no automatic relative sheet references (eg Sheet1-1). There
are workarounds, but I'm not sure if they fit your situation. If you have a
report sheet that you want to display data from other sheets in your workbbok
and those sheets are all structured identically, try this option:

Replace your direct references with indirect references.
For instance, if you want cell C5 on your report to refer to cell D10 on
some other sheet, change your formula
from this: ='00-01'!D10
to this: =INDIRECT(A1&"!D10")
Whatever sheet name you type in cell A1 will cause that formula to refer to
that sheet. If you enter 02-03 in cell A1, the formula in C5 will refer to
'02-03!D10.

There are other options, but let's start with that.

Does that help?

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

XL2002, WinXP-Pro


"Velson" wrote:

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.

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
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
Daily Totals on a summary sheet Allewyn Excel Worksheet Functions 10 June 27th 06 04:47 PM
Getting contents of a cell when cell reference is in the sheet A Nelson Excel Discussion (Misc queries) 3 October 5th 05 06:46 PM
Questions on copying from one sheet to the other and coping every other cell. KatyLady Excel Discussion (Misc queries) 10 June 7th 05 08:29 AM
Cell reference problems with Summary sheet McIntyre Excel Worksheet Functions 3 December 30th 04 06:29 PM


All times are GMT +1. The time now is 12:52 AM.

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"