Copying a Rang

  • Thread starter Thread starter JP
  • Start date Start date
J

JP

How do you properly copy a range of cells into a new worksheet or for
that matter into a different place in the same worksheet without
losing your reference cells.

I have ASAP utilities, and this box comes up but I don't understand
what they're asking and I could never get it to work.

I want to copy a range to a new worksheet, but it changes the
references.
 
You need to make your references absolute. You do this by entering a $ sign
before the row or column you want to stay the same. If always cell A1,
enter $A$1. If always row 1, but the column is relative to the cell, then
enter A$1. to make the column reference absolute, enter $A1. When in a
formula, hitting F4 will toggle your references from absolute, to mixed ,
then back to relative.

HTH

ryanb.
 
I tried this but it doesn't work when you want to copy the range to a
new worksheet or new workbook. This would only work for me when I
copied to the same worksheet.
 
I just tried it again only this time just using one cell. Here is the
formula:

=SUM($M$25:$M$2005)

I opened a new worksheet and tried posting it in several different
cells. The value it returns is $0.00.

I tried copying from the formula bar and from the cell itself--same
answer--$0.00.
 
Include the sheet name in your formula:
i.e. if named Sheet1

=SUM(Sheet1!$M$25:$M$2005)

Should work when copied to other sheets and workbooks.

HTH

ryanb.
 
That was the whole point of my original post. Is there a way to copy
a range without having to go into each individual cell and enter the
sheet reference?
 
By reading your OP, I would not have guessed what you wanted, so it really
wasn't too clear.

Anyway, the best way to accomplish this depends on exactly what names you're
using for your sheets.
If you're using XL's default names (Sheet1, Sheet2, ... etc.), this should
work:

=SUM(INDIRECT("Sheet"&ROWS($1:1)&"!M25:M2005"))

Copy down as needed.
This starts at Sheet1, and increments from there.
Since the cell references are enclosed in quotes, you don't need the
absolute references.

If your sheets are *not* the XL default names, post back with exactly the
names you're using.
 
If you name the cell (Insert>Name>Define) then you can simply type the
name into the new worksheet. If you had both workbooks open then you
could create a cell reference and when you closed the original
workbook, the second book would update the reference with the full
path/sheet.

For example, if you put "=SUM(Sheet1!$M$25:$M$2005)" in A1 and named
the cell "SumThis" you could go to another worksheet and type
"=SUMTHIS". You wouldn't have to edit the cell to update the sheet
reference.

HTH,
JP
 
Two people with the same handle? ! ? ! ? !
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
If you name the cell (Insert>Name>Define) then you can simply type the
name into the new worksheet. If you had both workbooks open then you
could create a cell reference and when you closed the original
workbook, the second book would update the reference with the full
path/sheet.

For example, if you put "=SUM(Sheet1!$M$25:$M$2005)" in A1 and named
the cell "SumThis" you could go to another worksheet and type
"=SUMTHIS". You wouldn't have to edit the cell to update the sheet
reference.

HTH,
JP
 
I assumed it was a formula you were dragging down which meant you only type
the sheet name in once and then autofill down or across or whatever. My
mistake. Are you simply trying to duplicate the calculation you have on the
origninal sheet, using the same data?

Have you tried COPY>PASTE SPECIAL>PASTE LINK?

Perhaps you could post an example if this or RD's solution are not what you
are looking for. Some extra clarity will get you a solution faster.

ryanb.
 
LOL and in the same thread, too. :-)

ps- on my screen the handles are different colors, hope that helps you
tell the difference!
 
I also have the headers of my own posts display in a different "watched"
color from the normal RED.

Of course, just like you, only I see this "Rule" governed format.
 
If you name the cell (Insert>Name>Define) then you can simply type the
name into the new worksheet. If you had both workbooks open then you
could create a cell reference and when you closed the original
workbook, the second book would update the reference with the full
path/sheet.

For example, if you put "=SUM(Sheet1!$M$25:$M$2005)" in A1 and named
the cell "SumThis" you could go to another worksheet and type
"=SUMTHIS". You wouldn't have to edit the cell to update the sheet
reference.
Click to expand...


I assume if you did this then you could just drag that cell (in your
new worksheet) and fill in your range?
 
Back
Top