Move/Copy A Row Based on Formulas to a New Worksheet

I want to move several rows of sub-totals (averages within sub-groups) to a 
summary worksheet, but I get the Ref error. How can I copy sub-group averages 
to another worksheet?

Thank you.
0
mtrplato (3)
11/19/2004 7:07:04 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
569 Views

Similar Articles

[PageSpeed] 47

high light and copy.
select where you want it.
edit>paste special>values.
this will turn you formulas into hard numbers.
you are getting the #Ref error because on the other sheet 
where you pasted the formulas, the formula no longer had 
the same references that they had on the other sheet.
for example:
=sum(a1:a10) in cell a11
you copy and paste on another sheet at cell a1.
excell tries to compensate but no longer has 10 cell above 
the formula. #Ref results. excel lost it's references that 
in had on the other sheet.

>-----Original Message-----
>I want to move several rows of sub-totals (averages 
within sub-groups) to a 
>summary worksheet, but I get the Ref error. How can I 
copy sub-group averages 
>to another worksheet?
>
>Thank you.
>.
>
0
anonymous (74722)
11/19/2004 8:34:44 PM
Reply:

Similar Artilces:

loop through pivottables columns and rows
I have a pivot table that have 2 columns that I need to loop through and read both columns and each items of each columns...the pivotitems only allow reading one column...does anyone know how to programmatically read both columns? Thanks You can loop through the column fields and their items: Sub test() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.ColumnFields For Each pi In pf.PivotItems Debug.Print pf.Name & " - " & pi.Name Next pi Next pf End Sub hpham77 wrote: > I have a pivot ta...

New with Outlook 2003 and IMAP
We have just been directed that we have to start using Outlook 2003. When I mark an email for trash or junk, I get a line through the topic and it gets grayed out. Is there a setting I can use that will put the email into trash or junk when I identify it as trash or junk? I am also trying to put my address book together. I need to find a way to have any email coming in with the same address (ex: occc_community@sunyorange.edu) or (staffchair-bounce@ots.sunyorange.edu) be made into a rule and make sure it goes to the appropriate folder. I am setup using an IMAP configuration. Any help would b...

Worksheet Changes
Hi All, HYCH Have a worksheet that has data in the range A5:I105, would like cell a2 to show a date that any of this data has changed, was thinking of using a worksheet change_Event to manage this but not sure of how to go about setting this up, have used the simple change event for a single selection i.e A1 or B4 but not with a range area. Any help would be Great Steve A bit more explanation along with layout and what desired output looks like. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Steve" <instructorf@hotmail.com> wrote in mes...

Pivottable Row SubTotals
I have just started using pivot tables, and have created one with sub totals at the end of each row. Is there a way of getting the totals to be at the begining of each row? Thanks in advance. That isn't an option provided by pivot tables. -- Regards, Tom Ogilvy Matthew <matthew@coxagri.com> wrote in message news:a69014c2.0310091309.59241b0f@posting.google.com... > I have just started using pivot tables, and have created one with sub > totals at the end of each row. > > Is there a way of getting the totals to be at the begining of each > row? > > Thanks in ad...

New Office reminds me of New Coke
Everyone hates it. The difference is that at least with Coke, there was Pepsi. Dear MS: Please admit that his was a failure and provide a patch that allows the world to toggle back to Classic Office. Stop being so arrogant. Did you really think we were going to pay billions to re-train everyone in a flippin' recession? Geez. Engineers.... It doesn't make you bad or dumb to admit you made a mistake. It means that you learned! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this su...

New CRM MVP's
I'd like to welcome our 2 new CRM MVP's: Aaron Elder & Matt Wittemann, welcome to the fold. -- Matt Parks MVP - Microsoft CRM Well come Aaron Elder and Matt Wittemann. /Aami Hi Matts i like to welcome both Aaron and Matt wittemann ..... i want to know hat is MVP .. can u please tell me about this Most Valuable Professional I know that .. i wanted to details about it sorry if i am hurting anybody ... http://mvp.support.microsoft.com/mvpexecsum Thanks ...... Aaron & Matt - Welcome aboard! Larry -- Larry Lentz [CRM MVP] MCSE+I, MCSE W2K, MCDBA, MCSA Me...

Drop-down Lists and Worksheet Protection
In Excell 2000 I could set "Validate/Drop down List" for a protected cell and when the worksheet was protected the cell could be "edited" using the drop-down list but not otherwise. Moreover, contents of the cell could be changed but not cleared. Try as I may I can't sem to achieve the same in Excell 2003. Any one have a solution? In Excel 2000, you can select from a data validation list if the source was a worksheet range, but not if the source was a delimited list. In Excel 2003, neither is allowed on a protected sheet. Perhaps you could use a combo box, instea...

Formula to Autofill Info based on Other Data
Using: Excel 2000, on Windows 98 Good morning! I'm hoping that someone on here can possibly help me. I have basic Excel knowledge, but this is out of my skill set. My boss has given me a project to work on, in which I have a workbook with two worksheets that I am dealing with. The worksheets are labelled PRICE and SUMMARY. On the PRICE sheet, I have several columns. Column A lists the product name, Columns E list the per page charge for the *red* program, and Column G lists the per page charge for the *blue* program. Those are the columns we will be dealing with. All specific inform...

Embedded Excel worksheet in PowerPoint
I'm working with an embedded excel worksheet in powerponit. The doc is sent to me and if I have to edit the worksheet, I find that some cells have missing characters once I open the worksheet to edit it. It appears that characters beyond some length (256?) are being truncated off. How do I resolve this so I don't have this problem again? Thanks, Barb Reinhardt This kind of problem may occur if the material was inserted into the embedded worksheet via copy/paste from an external sheet. A reliable approach is to open the embedded sheet first, and from that sheet do: File ...

Worksheet disappear when saving on shared workbook
Im Using Excel 2000 SP3, when i try to copy a cells from another workbook and paste it on the shared workbook, the worksheet of the shared workbook disappeared after saving it. Please help. ...

Inserting Rows Problem
Hi there, I have a list of about 2000 rows. However, in between each of thes rows I want to insert a blank row that can be used for people to writ in once the list is printed off. Is there a quick way to do this? Kind regards Andre -- andyp16 ----------------------------------------------------------------------- andyp161's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1165 View this thread: http://www.excelforum.com/showthread.php?threadid=26611 If you insert rows it will affect sorting, etc. and be a real pain. Many posts here want to delete blank rows ...

Purge data based on another Excel file???
I am attempting to purge data from a large list based on data in another document. I have the files I want to remaove in on file and the file I want to remove them from. There should be simple way to do this no? ...

Moving mailboxes using Active Directory Users and Computers
This is a multi-part message in MIME format. ------=_NextPart_000_0022_01C61DC8.6FAD4950 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Question: When moving mailboxes using AD Users and Computers from = Exchange server A to Exchange Server B, what will be the effect on users = that currently have their mailbox open in Microsoft Outlook? Some of the = users have Outlook 2003 and some of them have Outlook 2002. Is it best = to do the move when noone is on? but the thing is in this organization, = we have so laptop users that may not be on...

Only Running New Email Noficication
How do I receive new email notifications in the windows notification area without having the full Outlook 2002 program running all the time? (and thus using up valuable resources? We have an exchange server and are not on the same active directory as the server. Thank you for any help! ...

Copying text value based on long (comlex) calculation
I am not sure how to even search for what I want to do, so please forgive me if my first post has been discussed and resolved ad naseum in the past... Here is my situation: - I have a workbook that contains multiple sheets. - Each sheet contains auto parts from different suppliers, sorted by part number. - Some suppliers have parts that overlap with each other and the various sheets are updated as pricing and availabilty of parts change. - I am using a combination of the VLOOKUP and MIN functions to determine which supplier has lowest cost based on a comparison of part numbers and price ac...

I want a formula to ignore text values in cell references
If text is input into some cells referenced by a formula I want the formula to ignore whatever text it finds and just calculate the result of the numbers in cells. How do i do this? Presently the formula displays #VALUE! where there is text in one or more of the cells referenced. Use ISNUMBER(): In place of A1 use =IF(ISNUMBER(A1),A1,"") -- Gary''s Student "Russellrupert" wrote: > If text is input into some cells referenced by a formula I want the formula > to ignore whatever text it finds and just calculate the result of the numbers > in cells...

selecting rows of data
I have an excel worksheet with 4 columbs. I want to automatically select multiple rows of data according to search which will partly match one cell in the row and put the full ro as results in a new worksheet. for example 30/01/03,New York,233.85,complete 20/03/03,New Hampshire,188.22,complete 22/03/03,Texas,90.78,incomplete 23/04/03,Oregon,467.99,complete If the search string is "New" how can I produce a new worksheet whic will contain 30/01/03,New York,233.85,complete 20/03/03,New Hampshire,188.22,complete Any suggestions would be appriciated -- Message posted from http://w...

Formula Problem
Hi I'm struggling with a formula in an excel sheet I'm working on. I recently created a time sheet for the people in my work. One person however get public holidays a different method from everyone else - she only gets half a working day. My initial formula that works fine is =IF(OR(B12="A/L",B12="S/L",B12="P/H"),7/24,IF(B12="FLEXI",0,C12-B12- D12)) My ammended ones is =IF(OR(B12="A/L",B12="S/L"),7/24,IF(B12="P/H",3.5/24),IF(B12="FLEXI", 0,C12-B12-D12)) However it doesn't want to w...

How to make A1 the active cell in all visible worksheets ?
This is a multi-part message in MIME format. ------=_NextPart_000_0028_01CA8164.76257740 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ......so that when i activate the sheet A1 is the cell in the upper left = corner of my screen. Thanx ------=_NextPart_000_0028_01CA8164.76257740 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; ...

data links on subsequent worksheets
I have main data entered on worksheet 1 that I have linked to subsequent worksheets, however when there is no data on worksheet 1 I get 0's printed in the data space. How doI eliminate the 0? If you want to hide all zero values on the sheet, Tools --> Options --> [View] tab and clear the checkbox next to "Zero Values" In Excel 2007, that's "Office Button" --> [Excel Options] --> then [Advanced] in left pane and scroll down to the "Display Options for this Worksheet" and clear the box next to "Show a zero in cells that have a...

Delete duplicate rows from a list in Excel
Hi, Below are instructions on how to "delete duplicate rows from a list in Excel". I learned about this tip from the Microsoft Office Assistant website. However, each time I try step 4, my list is not filtered and no records are hidden. Is there a secret I am missing to make this work? Thanks for your help! -Greg http://office.microsoft.com/en-us/assistance/HA010346261033.aspx Applies to Microsoft Office Excel 2003 Microsoft Excel 2002 A duplicate row (also called a record) in a list is one where all values in the row are an exact match of all the values in another row. To delet...

Nested query-based distribution groups?
We are currently trying to implement some query-based distribution groups. Is it possible to create one QBDG that has a couple of rules (for example all people with a certain job title) and then create another dynamic group that will start with all the users in the previous group (all people with a certain title) and further narrow it down with more rules? Basically does exchange 2003 allow for nested query-based groups? If so, how do you go about doing it? Thanks. Yes they can be nested. You would nest them just like you would nest any other group. http://support.microsoft.com/?id=82289...

IF formula problem
I need to add categories to a downloaded bank statement. I would use something like Pascal's CASE formula: CASE(lookup value)= a: do x b: do y c: do z etc... Excel doesn't have such formula. Therefore, I use the following nested IF formula: =IF(ISERROR(SEARCH('Netbank Cat names'!$A$2,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$3,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$4,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$5,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$6,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$7,B73)),"&...

*** HOT --- NEW VIRUS ALERT ****
Has anyone seen a new virus going around. We are seeing an email with a zip file that is labeled price.zip or newprice.zip or 08_price.zip or price_new.zip. Within the zip file is a html file called price.html and a folder called price with an executable of price.exe. This thing has it's own SMTP engine and is starting havoc on our network. ANYONE SEEN THIS OR KNOW HOW TO REMOVE IT? for the time being, you might want to block zip files from coming in...sounds like a bagle variation...see here: http://vil.nai.com/vil/content/v_127423.htm "Fred Yarbrough" <fcyarbroug...

rows and columns in spreadsheet
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Is there a way, in Excel, that I can merge cells, rows, or columns, like I can in Numbers? Many thanks for your help. ...