 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008Copying a Rang
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. > 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 [quoted text clipped - 5 lines] > I want to copy a range to a new worksheet, but it changes the > references. 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. >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, [quoted text clipped - 15 lines] >> I want to copy a range to a new worksheet, but it changes the >> references. Works for me. If I copy =$A1$B1 on sheet1 to sheet2, it remains the same Tyro >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 [quoted text clipped - 20 lines] >>> I want to copy a range to a new worksheet, but it changes the >>> references. 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. >Works for me. If I copy =$A1$B1 on sheet1 to sheet2, it remains the same > [quoted text clipped - 24 lines] >>>> I want to copy a range to a new worksheet, but it changes the >>>> references. 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. >I just tried it again only this time just using one cell. Here is the > formula: [quoted text clipped - 37 lines] >>>>> I want to copy a range to a new worksheet, but it changes the >>>>> references. 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? >Include the sheet name in your formula: > i.e. if named Sheet1 [quoted text clipped - 48 lines] >>>>>> I want to copy a range to a new worksheet, but it changes the >>>>>> references. 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.  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > 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 [quoted text clipped - 57 lines] >>>>>>> I want to copy a range to a new worksheet, but it changes the >>>>>>> references. 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 > 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 [quoted text clipped - 6 lines] > > >Should work when copied to other sheets and workbooks. Two people with the same handle? ! ? ! ? ! SignatureRegards, 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 > 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 [quoted text clipped - 6 lines] > > >Should work when copied to other sheets and workbooks. LOL and in the same thread, too. :-) ps- on my screen the handles are different colors, hope that helps you tell the difference! > Two people with the same handle? ! ? ! ? ! > -- > Regards, > > RD 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. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- LOL and in the same thread, too. :-) ps- on my screen the handles are different colors, hope that helps you tell the difference! On Jan 11, 4:02 pm, "RagDyer" <ragd...@cutoutmsn.com> wrote: > Two people with the same handle? ! ? ! ? ! > -- > Regards, > > RD >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 [quoted text clipped - 6 lines] >"=SUMTHIS". You wouldn't have to edit the cell to update the sheet >reference. I assume if you did this then you could just drag that cell (in your new worksheet) and fill in your range? >HTH, >JP [quoted text clipped - 9 lines] >> >> >Should work when copied to other sheets and workbooks. 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. > 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 [quoted text clipped - 57 lines] >>>>>>> I want to copy a range to a new worksheet, but it changes the >>>>>>> references. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.