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.
mtrplato
excel.newusers
15348 articles. 
2 followers.
1 Replies
504 Views
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. >. >
anonymous
Similar Artilces:
Place a button on a worksheet and assign a macro to it?
How do I place a single "button" on a worksheet (say, in one cell), and assign a macro to it? Hi, Choose View, Toolbars, and pick the Forms toolbar. Click the Button tool and then click in the spreadsheet. You will be prompted for the macro you want to assign. You can resize and move the button after assigning the macro. If this helps, please click the Yes button Cheers, Shane Devenshire "PFB" wrote: > How do I place a single "button" on a worksheet (say, in one cell), and > assign a macro to it? I often use shapes from the drawing tool bar. O...
Grouping to included ranges for which no base data exists
I have a set of data which has numbers ranging from 1700 to 32100. I'd like to pivot table them and group them in bands of 100, starting at 1700. I'd like my pivot table to show all of the bands that exist. The problem is, if I have no data within the band 2200 to 2300 that band wont appear in my grouped pivot table. Any help most appreciated. Thanks in advance. Gerry. ...
Formula?
I'm trying to add a formula in a cell which will leave the cell blank if column A is blank. I use the following: ActiveCell.FormulaR1C1 = "=if(+RC[-10]>0,2.5,"")" but I get a run-time error (1004). I know the problem is the quotes in the third argument. I tried replacing the quotes with zero and it enters zero correctly with no error. But I need the cell to be blank, not zero, if column A is blank. Hi Try four quotes instead of two: "=if(+RC[-10]>0,2.5,"""")" -- HTH. Best wishes Harald Followup to newsgroup only please ...
Error 80070057
H I just installed a Exchange 2003 box as new server of a Exchange 5.5 site (exdeploy-tools) I didn't received any errors during install and mail flow between servers My issue is when I view the RUS properties it shows only Descriptio properties and when I try to generate a new RUS service I get the erro Exchange System Manager Invalid Argumen Facility: Win3 ID No: 8007005 Exchange System Manage I received the same error message when I try to generate a new Address Lis What is the problem The exchange service account is member of the following groups: local administrators, domain a...
How can I change worksheet direction from right to left
I want to change the direction of the work sheet in Excel 2007 from left to right and vice versa If you mean you want to change the direction of the cursor on Enter, then click on the round logo top left, select Excel options, select Advanced, and change the direction to Left, Right, Up or Down as you desire. -- HTH Kassie Replace xxx with hotmail "Haitham" wrote: > I want to change the direction of the work sheet in Excel 2007 from left to > right and vice versa I believe the only way is to go through Microsoft Office Tools and make the setting ...
Rules Based On Numeric Field Values
Hi, I sometimes get emails which have special standardized notifications in them. Any "special attention" areas are in a consistent format which will also contain numbers that are normally 0. If they aren't 0, then there is some kind of problem. So, the number values can be from 0 to any value. Is there a way to "flag" these in a rule? Ideally, I'd like to change the message color of or priority based on the values seen. Below is partial output from one of the emails with example fields high-lighted: Successes : 10 Failures : 0 <==== Aborted : ...
Excel 2003
Whenever a colleague updates a spreadsheet I've created it comes back 10MBytes large whereas it was originally only 50KBytes. It looks to me as if the colleague accidentally adds tens or hundreds of thousands of empty rows. How can I delete all rows below a certain row. Delete does not work. Copying the real rows into a new worksheet loses the formatting. When I apply the formatting from the big spreadsheet (paste formatting) I end up with hundreds of thousands of rows again :-( Any suggestions? Tom Selecting all the rows below your actual data. Right-click, delete cells. (this is...
Hidden tabs on my worksheet
I can't figure out how my settings changed or how to change them back. When i go to Tools: Options: View: Tabs - it is checked. Anyone have any other ideas on how to get my tabs to show? Have even tried changing the color of them... Thanks! hi have you tried.. on the menu bar>window>unhide Regards FSt1 "Courtney" wrote: > I can't figure out how my settings changed or how to change them back. When i > go to Tools: Options: View: Tabs - it is checked. Anyone have any other ideas > on how to get my tabs to show? Have even tried changing ...
Copying junk senders list
Hi, In Outlook 2000 and 98, how do you copy a junk senders lists to put on another client account? Thanks ...
Understanding complex IF Formula within IF formula
I do not know what this formula is doing can you help? =IF(LEFT(QBCash!$B2,5)="Total","",IF(ISBLANK(QBCash!$B2),$A1,QBCash!$B2)) Thanks for your help. DP Hi, The formula is looking at a worksheet called QBCash Breaking the formula down =IF(LEFT(QBCash!$B2,5)="Total","", 1. It looks at the contents of B2 and if the word 'Total' are the first 5 letters of the cell the formula return a null string IF(ISBLANK(QBCash!$B2),$A1, 2. If that isn't TRUE it then checks if B2 is blank and if it is it returns the value of...
How to change font in Status row and Tooltip
Hi! Is it possible to change the font in the status field and on tooltips that gets displayed. I'd like to change to a font that can display Cyrilic characters. Thanks in advance! /Henke ...
Combobox Help needed
Hello, I have been trying to populate a Combobox with a filter set to what is being typed in the combo's edit control. If I type in "C" it selects the first 40 "C" then I would like to type in "u" clear the listbox portion and put in the first 40 "Cu" etc.. I would need to clear only the combo's edit box but unfortunately ResetContent()also clears the edit box. It gets the first 40 "C" but then the edit box is cleared. I have also tried using DeleteString. void CCustView::OnEditupdateCombo1() { UpdateData(); if (i>0) { m_CbCust....
moving colors?
I am setting up a spreadsheet to monitor diaries at work. I am fine with everythig except copying colors form one cell ref to another. I have an"input" page where you put staff details, interview names, an abbreviation for the interview and the length of the interview. Then i have 5 pages one for each day. The spreadsheet calculates at the bottom of the day page the numbers of interviews and time spent interviewing and all of this works fine. I have a list at the bottom that pulls info from the input page and this is where i hit a wall. It pulls the interview type but how do ...
How to make a worksheet visible
hi I have about 40 worksheets in a workbook. each sheet has a name of a student in a classroom. how can I programaticy make a choosen sheet to be visible thanks Yosi Sub unhide() Dim whichname as String whichname = InputBox("type in name") Worksheets(whichname).Visible = True End Sub Gord Dibben MS Excel MVP On Tue, 23 Jan 2007 20:39:23 +0200, "yosi_lb" <yosi_lb@bezeqint.net> wrote:Sub >hi >I have about 40 worksheets in a workbook. each sheet has a name of a student >in a classroom. >how can I programaticy make a choosen sheet to ...
How to move Ex2k3 Sp1 between Admin Groups
All my Exchange 2003 servers have Sp1 applied. (we are still in Mixed Mode). How do I move a Exchange "003 server from an admin group with only itself and an Exchange 5.5 server init to another admin group with both Exchange 2003 & 5.5 servers in it ? 1) How do you actually do it ? 2) Should I remove the Exchange 5.5 Server first or remove it after the move ? Thank's Godfrey You cannot move servers between admin groups. You can move users between admin groups, though if: a) You're in Exchange native mode or b) You use the site consolidation tools in Exchange 2003 SP1...
Calculations based on PivotTable information
I am trying to create calculations for Commissions based on Pivot Table information. However, When i try to filter(change the dimension) to show individual Salespeople the columns that i refer to in my calculations change, thus my calculations are in accurate. This is what i am using to calculate when all salespeople are present on the pivot table In Column J IF(AND(ISNUMBER(SEARCH("*total",C5)),F5<>""),F5*0.03,"") In Column L IF(AND(ISNUMBER(SEARCH("*total",C5)),G5<>""),G5*0.02,"") any help would be appreciated. ...
formulas and external data
i have an issue trying to calculate data based on a query i import the data with all my columns and records, at the end i insert a column with a formula that calculates 4 different cells in the row then i run a query to remove null (zero) value data from a certain field, then the formual column that i added manually doesn't look right. for example, if i have 800 rows of data, i copied that formula to all 800 rows in the last column following the external data when i run the query, all the zero values are removed (doing what it should) and the formulas stay, except the last row that is...
USING DATES IN FORMULAS #2
I foubd a problem with excel 2003 If you introduce a formula using dates (in spanish version) (dd/mm/yy), excel doesnt makes the calculation........... ???? Can somebody hep me In the USA version (English), I could use: =if(a1=date(2005,10,27),"It's Oct 27, 2005","it's not") to refer to a date. I don't know what the Spanish function is for =date(), though. Carlos Benavides wrote: > > I foubd a problem with excel 2003 > If you introduce a formula using dates (in spanish version) (dd/mm/yy), > excel doesnt makes the calculation........... ????...
Moving the cursor in an editbox
Hi all! I am trying to figure out how to move the cursor to the end of the text in an editbox as soon as I have updated it with text using the UpdateData() command, so the user can immediately append more text. Please could someone give me a hint as to how to go about doing this? Any help would be much appreciated. Best Regards. Daz "Daz" <cutenfuzzy@gmail.com> wrote in message news:1150022788.432768.3710@c74g2000cwc.googlegroups.com... > Hi all! > > I am trying to figure out how to move the cursor to the end of the text > in an editbox as soon as I have updat...
Getting any formula to stop at the next blank space
I frequently write VLOOKUP and SUMIF formulas for my spreadsheets an have found myself typing the same thing over and over. How can I ad in a command that makes each formula stop at the next blank. I als have to make the formula references "absolutes". What I'm wanting t do is copy and paste the same formula down without sacrifices th absolute references -- ajpower ----------------------------------------------------------------------- ajpowers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=940 View this thread: http://www.excelforum.com/showth...
building an external link based on a cell value
I would like to build an external link in a spreadsheet that automatically changes based on a cell value. For example, I have the following link: =SUMIF('[Location Income Statements 2004.xls]Mar04'! $14:$14,P5,'[Location Income Statements 2004.xls]Mar04'! $18:$18) Instead of Mar04 in the formula above, I would like to use a cell's contents.... cell A8 for example. Is this possible? Thanks, Jason Jason You can use the INDIRECT function. =SUMIF(INDIRECT("'[Location Income Statements 2004.xls]" & A8 & "'!$14:$14"),P5... Indirect ...
new emails don't show without clicking send-receive
new emails don't show without clicking send-receive button--and now I've lost the send/receive button. What do I do? thanks ...
Repeat formula in new rows
I have created a simple spread sheet to keep track of finances. Each day I want to enter a stock price at the beginning of a new row and then have about 8 columns adjust their amounts in the new row. When I enter a new stock price at the beginning of a new line and press enter, only two of the 8 columns tabulate and produce new amounts. The two columns that do act correctly have these formulas on line 14 for example: F14 has: =50000*B14 J14 has: =F14-187492.97 Some of the ones that do not adjust and stay blank have these formulas on line 13: C13 has: =B13-B12 but there is nothing i...
Copy entire column
I am trying to Copy the entire column from one sheet in a workbook to another sheet in the same workbook I have tried the following Code: -- Sub CopyYear1() Workbooks("Book1.xls").Sheets("Sheet1").Range("A").Copy _ Workbooks("Book1.xls").Sheets("Sheet2").Range("A") End Sub And I get a run time error '9' when I run it. I am a newbie when it comes to code any help would be appreciated. Thanks Darrin Sand Project Manager MEC Services Inc Phone: 701-337-5404 Cell: 701-240-4000 email: darrinds@mcleanelectric.co...
Reflect cell contents into another worksheet #3
I have forgotten how to reflect data that is present in one worksheet cell into a specific cell of a second worksheet. IOW, repeat cell contents in a second worksheet. I simply do not make use of MS Excel97 often enough to remember the procedures ! Please help. Regards, ~ Vince ~ You can just type = and then click the cell if you want to be duplicate. "Vince" wrote: > > I have forgotten how to reflect data that is present in one worksheet > cell into a specific cell of a second worksheet. IOW, repeat cell > contents in a second worksheet. > > I simply do...
How do I place a single "button" on a worksheet (say, in one cell), and assign a macro to it? Hi, Choose View, Toolbars, and pick the Forms toolbar. Click the Button tool and then click in the spreadsheet. You will be prompted for the macro you want to assign. You can resize and move the button after assigning the macro. If this helps, please click the Yes button Cheers, Shane Devenshire "PFB" wrote: > How do I place a single "button" on a worksheet (say, in one cell), and > assign a macro to it? I often use shapes from the drawing tool bar. O...
Grouping to included ranges for which no base data exists
I have a set of data which has numbers ranging from 1700 to 32100. I'd like to pivot table them and group them in bands of 100, starting at 1700. I'd like my pivot table to show all of the bands that exist. The problem is, if I have no data within the band 2200 to 2300 that band wont appear in my grouped pivot table. Any help most appreciated. Thanks in advance. Gerry. ...
Formula?
I'm trying to add a formula in a cell which will leave the cell blank if column A is blank. I use the following: ActiveCell.FormulaR1C1 = "=if(+RC[-10]>0,2.5,"")" but I get a run-time error (1004). I know the problem is the quotes in the third argument. I tried replacing the quotes with zero and it enters zero correctly with no error. But I need the cell to be blank, not zero, if column A is blank. Hi Try four quotes instead of two: "=if(+RC[-10]>0,2.5,"""")" -- HTH. Best wishes Harald Followup to newsgroup only please ...
Error 80070057
H I just installed a Exchange 2003 box as new server of a Exchange 5.5 site (exdeploy-tools) I didn't received any errors during install and mail flow between servers My issue is when I view the RUS properties it shows only Descriptio properties and when I try to generate a new RUS service I get the erro Exchange System Manager Invalid Argumen Facility: Win3 ID No: 8007005 Exchange System Manage I received the same error message when I try to generate a new Address Lis What is the problem The exchange service account is member of the following groups: local administrators, domain a...
How can I change worksheet direction from right to left
I want to change the direction of the work sheet in Excel 2007 from left to right and vice versa If you mean you want to change the direction of the cursor on Enter, then click on the round logo top left, select Excel options, select Advanced, and change the direction to Left, Right, Up or Down as you desire. -- HTH Kassie Replace xxx with hotmail "Haitham" wrote: > I want to change the direction of the work sheet in Excel 2007 from left to > right and vice versa I believe the only way is to go through Microsoft Office Tools and make the setting ...
Rules Based On Numeric Field Values
Hi, I sometimes get emails which have special standardized notifications in them. Any "special attention" areas are in a consistent format which will also contain numbers that are normally 0. If they aren't 0, then there is some kind of problem. So, the number values can be from 0 to any value. Is there a way to "flag" these in a rule? Ideally, I'd like to change the message color of or priority based on the values seen. Below is partial output from one of the emails with example fields high-lighted: Successes : 10 Failures : 0 <==== Aborted : ...
Excel 2003
Whenever a colleague updates a spreadsheet I've created it comes back 10MBytes large whereas it was originally only 50KBytes. It looks to me as if the colleague accidentally adds tens or hundreds of thousands of empty rows. How can I delete all rows below a certain row. Delete does not work. Copying the real rows into a new worksheet loses the formatting. When I apply the formatting from the big spreadsheet (paste formatting) I end up with hundreds of thousands of rows again :-( Any suggestions? Tom Selecting all the rows below your actual data. Right-click, delete cells. (this is...
Hidden tabs on my worksheet
I can't figure out how my settings changed or how to change them back. When i go to Tools: Options: View: Tabs - it is checked. Anyone have any other ideas on how to get my tabs to show? Have even tried changing the color of them... Thanks! hi have you tried.. on the menu bar>window>unhide Regards FSt1 "Courtney" wrote: > I can't figure out how my settings changed or how to change them back. When i > go to Tools: Options: View: Tabs - it is checked. Anyone have any other ideas > on how to get my tabs to show? Have even tried changing ...
Copying junk senders list
Hi, In Outlook 2000 and 98, how do you copy a junk senders lists to put on another client account? Thanks ...
Understanding complex IF Formula within IF formula
I do not know what this formula is doing can you help? =IF(LEFT(QBCash!$B2,5)="Total","",IF(ISBLANK(QBCash!$B2),$A1,QBCash!$B2)) Thanks for your help. DP Hi, The formula is looking at a worksheet called QBCash Breaking the formula down =IF(LEFT(QBCash!$B2,5)="Total","", 1. It looks at the contents of B2 and if the word 'Total' are the first 5 letters of the cell the formula return a null string IF(ISBLANK(QBCash!$B2),$A1, 2. If that isn't TRUE it then checks if B2 is blank and if it is it returns the value of...
How to change font in Status row and Tooltip
Hi! Is it possible to change the font in the status field and on tooltips that gets displayed. I'd like to change to a font that can display Cyrilic characters. Thanks in advance! /Henke ...
Combobox Help needed
Hello, I have been trying to populate a Combobox with a filter set to what is being typed in the combo's edit control. If I type in "C" it selects the first 40 "C" then I would like to type in "u" clear the listbox portion and put in the first 40 "Cu" etc.. I would need to clear only the combo's edit box but unfortunately ResetContent()also clears the edit box. It gets the first 40 "C" but then the edit box is cleared. I have also tried using DeleteString. void CCustView::OnEditupdateCombo1() { UpdateData(); if (i>0) { m_CbCust....
moving colors?
I am setting up a spreadsheet to monitor diaries at work. I am fine with everythig except copying colors form one cell ref to another. I have an"input" page where you put staff details, interview names, an abbreviation for the interview and the length of the interview. Then i have 5 pages one for each day. The spreadsheet calculates at the bottom of the day page the numbers of interviews and time spent interviewing and all of this works fine. I have a list at the bottom that pulls info from the input page and this is where i hit a wall. It pulls the interview type but how do ...
How to make a worksheet visible
hi I have about 40 worksheets in a workbook. each sheet has a name of a student in a classroom. how can I programaticy make a choosen sheet to be visible thanks Yosi Sub unhide() Dim whichname as String whichname = InputBox("type in name") Worksheets(whichname).Visible = True End Sub Gord Dibben MS Excel MVP On Tue, 23 Jan 2007 20:39:23 +0200, "yosi_lb" <yosi_lb@bezeqint.net> wrote:Sub >hi >I have about 40 worksheets in a workbook. each sheet has a name of a student >in a classroom. >how can I programaticy make a choosen sheet to ...
How to move Ex2k3 Sp1 between Admin Groups
All my Exchange 2003 servers have Sp1 applied. (we are still in Mixed Mode). How do I move a Exchange "003 server from an admin group with only itself and an Exchange 5.5 server init to another admin group with both Exchange 2003 & 5.5 servers in it ? 1) How do you actually do it ? 2) Should I remove the Exchange 5.5 Server first or remove it after the move ? Thank's Godfrey You cannot move servers between admin groups. You can move users between admin groups, though if: a) You're in Exchange native mode or b) You use the site consolidation tools in Exchange 2003 SP1...
Calculations based on PivotTable information
I am trying to create calculations for Commissions based on Pivot Table information. However, When i try to filter(change the dimension) to show individual Salespeople the columns that i refer to in my calculations change, thus my calculations are in accurate. This is what i am using to calculate when all salespeople are present on the pivot table In Column J IF(AND(ISNUMBER(SEARCH("*total",C5)),F5<>""),F5*0.03,"") In Column L IF(AND(ISNUMBER(SEARCH("*total",C5)),G5<>""),G5*0.02,"") any help would be appreciated. ...
formulas and external data
i have an issue trying to calculate data based on a query i import the data with all my columns and records, at the end i insert a column with a formula that calculates 4 different cells in the row then i run a query to remove null (zero) value data from a certain field, then the formual column that i added manually doesn't look right. for example, if i have 800 rows of data, i copied that formula to all 800 rows in the last column following the external data when i run the query, all the zero values are removed (doing what it should) and the formulas stay, except the last row that is...
USING DATES IN FORMULAS #2
I foubd a problem with excel 2003 If you introduce a formula using dates (in spanish version) (dd/mm/yy), excel doesnt makes the calculation........... ???? Can somebody hep me In the USA version (English), I could use: =if(a1=date(2005,10,27),"It's Oct 27, 2005","it's not") to refer to a date. I don't know what the Spanish function is for =date(), though. Carlos Benavides wrote: > > I foubd a problem with excel 2003 > If you introduce a formula using dates (in spanish version) (dd/mm/yy), > excel doesnt makes the calculation........... ????...
Moving the cursor in an editbox
Hi all! I am trying to figure out how to move the cursor to the end of the text in an editbox as soon as I have updated it with text using the UpdateData() command, so the user can immediately append more text. Please could someone give me a hint as to how to go about doing this? Any help would be much appreciated. Best Regards. Daz "Daz" <cutenfuzzy@gmail.com> wrote in message news:1150022788.432768.3710@c74g2000cwc.googlegroups.com... > Hi all! > > I am trying to figure out how to move the cursor to the end of the text > in an editbox as soon as I have updat...
Getting any formula to stop at the next blank space
I frequently write VLOOKUP and SUMIF formulas for my spreadsheets an have found myself typing the same thing over and over. How can I ad in a command that makes each formula stop at the next blank. I als have to make the formula references "absolutes". What I'm wanting t do is copy and paste the same formula down without sacrifices th absolute references -- ajpower ----------------------------------------------------------------------- ajpowers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=940 View this thread: http://www.excelforum.com/showth...
building an external link based on a cell value
I would like to build an external link in a spreadsheet that automatically changes based on a cell value. For example, I have the following link: =SUMIF('[Location Income Statements 2004.xls]Mar04'! $14:$14,P5,'[Location Income Statements 2004.xls]Mar04'! $18:$18) Instead of Mar04 in the formula above, I would like to use a cell's contents.... cell A8 for example. Is this possible? Thanks, Jason Jason You can use the INDIRECT function. =SUMIF(INDIRECT("'[Location Income Statements 2004.xls]" & A8 & "'!$14:$14"),P5... Indirect ...
new emails don't show without clicking send-receive
new emails don't show without clicking send-receive button--and now I've lost the send/receive button. What do I do? thanks ...
Repeat formula in new rows
I have created a simple spread sheet to keep track of finances. Each day I want to enter a stock price at the beginning of a new row and then have about 8 columns adjust their amounts in the new row. When I enter a new stock price at the beginning of a new line and press enter, only two of the 8 columns tabulate and produce new amounts. The two columns that do act correctly have these formulas on line 14 for example: F14 has: =50000*B14 J14 has: =F14-187492.97 Some of the ones that do not adjust and stay blank have these formulas on line 13: C13 has: =B13-B12 but there is nothing i...
Copy entire column
I am trying to Copy the entire column from one sheet in a workbook to another sheet in the same workbook I have tried the following Code: -- Sub CopyYear1() Workbooks("Book1.xls").Sheets("Sheet1").Range("A").Copy _ Workbooks("Book1.xls").Sheets("Sheet2").Range("A") End Sub And I get a run time error '9' when I run it. I am a newbie when it comes to code any help would be appreciated. Thanks Darrin Sand Project Manager MEC Services Inc Phone: 701-337-5404 Cell: 701-240-4000 email: darrinds@mcleanelectric.co...
Reflect cell contents into another worksheet #3
I have forgotten how to reflect data that is present in one worksheet cell into a specific cell of a second worksheet. IOW, repeat cell contents in a second worksheet. I simply do not make use of MS Excel97 often enough to remember the procedures ! Please help. Regards, ~ Vince ~ You can just type = and then click the cell if you want to be duplicate. "Vince" wrote: > > I have forgotten how to reflect data that is present in one worksheet > cell into a specific cell of a second worksheet. IOW, repeat cell > contents in a second worksheet. > > I simply do...