A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Copying a work sheet cell reference as relative not absolute?



 
 
Thread Tools Display Modes
  #1  
Old January 6th 06, 02:23 PM posted to microsoft.public.excel.misc
external usenet poster
 
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.
Ads
  #2  
Old January 6th 06, 02:39 PM posted to microsoft.public.excel.misc
external usenet poster
 
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
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


"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  
Old January 6th 06, 03:20 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Copying a work sheet cell reference as relative not absolute?

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!

"Ron Coderre" wrote:

> 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
>
>
> "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  
Old January 6th 06, 03:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
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 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!
>
> "Ron Coderre" wrote:
>
> > 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
> >
> >
> > "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  
Old January 7th 06, 01:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
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 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!
> >
> > "Ron Coderre" wrote:
> >
> > > 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
> > >
> > >
> > > "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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
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 05:29 PM
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 1 December 2nd 04 08:30 PM


All times are GMT +1. The time now is 02:33 AM.


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