Copying a work sheet cell reference as relative not absolute?

[Guest]

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.

 

[Guest]

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

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

Does that help? (if no...Edit>Undo)

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

XL2002, WinXP-Pro

 

[Guest]

Ron - thank you for the try. However:

1. The Find>replace 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!

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

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

Does that help? (if no...Edit>Undo)

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

XL2002, WinXP-Pro

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.

 

[Guest]

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

Ron - thank you for the try. However:

1. The Find>replace 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!

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

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

Does that help? (if no...Edit>Undo)

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

XL2002, WinXP-Pro

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.

 

[Guest]

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?

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

Ron - thank you for the try. However:

1. The Find>replace 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!

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

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

Does that help? (if no...Edit>Undo)

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

XL2002, WinXP-Pro


:

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.