Copy/Paste formulas

How do I keep certain cells (those I want to point to a specific 'constant') 
from incrementing while the remaing cells in my formulas increment as 
expected. Example: ((E65)*(COUNTIF(I7:I7,"V"))) where the cell "E65" contains 
a set value that I want to be placed in the result as I step down the 
incremental (I) rows when the character "V" is found in the particular (I) 
row. When I do my copy and paste, the (E) row increments as the (I) rows 
increment.
0
BobG (12)
2/18/2005 4:39:11 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
390 Views

Similar Articles

[PageSpeed] 9

$E$65

"BobG" wrote:

> How do I keep certain cells (those I want to point to a specific 'constant') 
> from incrementing while the remaing cells in my formulas increment as 
> expected. Example: ((E65)*(COUNTIF(I7:I7,"V"))) where the cell "E65" contains 
> a set value that I want to be placed in the result as I step down the 
> incremental (I) rows when the character "V" is found in the particular (I) 
> row. When I do my copy and paste, the (E) row increments as the (I) rows 
> increment.
0
2/18/2005 4:43:09 PM
You need to put the dollar sign ($) in front of the row and column reference 
you wish to make an absolute reference.i.e. $E$65. This will not be 
incremented when you copy and paste.

Jock W

"BobG" wrote:

> How do I keep certain cells (those I want to point to a specific 'constant') 
> from incrementing while the remaing cells in my formulas increment as 
> expected. Example: ((E65)*(COUNTIF(I7:I7,"V"))) where the cell "E65" contains 
> a set value that I want to be placed in the result as I step down the 
> incremental (I) rows when the character "V" is found in the particular (I) 
> row. When I do my copy and paste, the (E) row increments as the (I) rows 
> increment.
0
JockW (12)
2/18/2005 4:47:12 PM
Thanks to both Adam and Jock, for their timely response, it worked!

"BobG" wrote:

> How do I keep certain cells (those I want to point to a specific 'constant') 
> from incrementing while the remaing cells in my formulas increment as 
> expected. Example: ((E65)*(COUNTIF(I7:I7,"V"))) where the cell "E65" contains 
> a set value that I want to be placed in the result as I step down the 
> incremental (I) rows when the character "V" is found in the particular (I) 
> row. When I do my copy and paste, the (E) row increments as the (I) rows 
> increment.
0
BobG (12)
2/18/2005 5:11:06 PM
Reply:

Similar Artilces:

Copying XML data to Word
I have got an Excel XML spreadsheet which grabs data from Microsoft CRM. I have set it up to have 5 different sheets. I want to copy information from each of these sheets into Word, but for some reason, only data from the first sheet is copied. When I try to copy cells from other sheets, I get blank cells in Word. Can anyone tell me why? And even better, is there an automatic way to copy this stuff into a Word template so I don't have to manually select the cells? ...

Help simplifying a SUMPRODUCT formula
The following SUMPRODUCT formula produces the correct results but I'm reasonably certain that there must be a more efficient way of constructing the formula. There are basically two components to this formula separated by the + sign. Each component performs the same calculation: the first part for Class="MS" and the second part (after the +) for Class="MSTV". Isn't there a way to construct the formula so that it would be calculated for Class="MS" OR Class="MSTV" and eliminate the need for two steps? I tried to incorporate the OR formula wi...

Help With an Excel Formula
I am in excel and I have the following formula = Q:26 typed into Cell C:2. What I want to do is have a second cell D:2 that takes what ever cell reference I enter into C:2 and adds 1 row to it. So cell D:2 would be equal to Q:27, which is 1 row down from row Q:26. Is there a way to do this? Not possible unless you use an add-in or user define function that reads the text of the formula in C2 D McRitchie has UDF for that http://www.mvps.org/dmcritchie/excel/formula.htm then you could use =OFFSET(INDIRECT(SUBSTITUTE(getformula(C2),"=","")),1,) Regards, Peo Sjobl...

Copying screw-up
Tried to copy my settings and e-mails over from my pc to my laptop and I managed to get everything I need and one thing I don't! Every time I start Outlook I get an error mesage pop up with the header Personal Folders and the text 'The path specified for the folder I:\inmail backup.pst is not valid' I click "OK" and get a second box called Creat/Open Personal Folder showing My Documents and the File Name is inmail backup and the File Type is PST File (.*pst) Any idea how I can stop this appearing each time I start up? It's starting to irritate now! Thanks in ad...

Formula help needed
Can someone help me to shorten this formula, it slows down my computer so it takes ages to recalculate the sheet when I enter something in it. It is used to calculate time, basic formula (A2-A1+(A2<A1)) from C Pearsson�s site OFFSET(INDEX(Tid3;MATCH(A8&1;Feb!$A$9:$A$250&Feb!$B$9:$B$250;0);MATCH(Feb!$C $8;Feb!$A$8:$D$8;0);1);1;0)-INDEX(Tid3;MATCH(A8&1;Feb!$A$9:$A$250&Feb!$B$9:$ B$250;0);MATCH(Feb!$C$8;Feb!$A$8:$D$8;0);1)+(OFFSET(MATCH(Tid3;PASSA(A8&1;Fe b!$A$9:$A$250&Feb!$B$9:$B$250;0);MATCH(Feb!$C$8;Feb!$A$8:$D$8;0);1);1;0)<IND EX(Tid3;MATCH(A8&1;Feb!$A$9:$A$...

Copying olOLE Attachments in Outlook
Hi There, I am looking for a way to copy an Attachment of type olOLE from one contact to another using VB.net. Any suggestions would be greatly appreciated. I have all other types copying no problem, but I cannot get an olOLE attachment to work. I have tried adding it as the object in the .Attachments.Add method but I get an exception "Member not found". Here is the code I have, that doesn't work. (The type of object I am testing with is a birthday reminder to the calendar). newContact.Attachments.Add(contactAttachment, Outlook.OlAttachmentType.olOLE, , "") Ev...

FORMULAS please help
Hi there I am trying to create a formula that refences <> numbers in a columm then adds up the from a different columm i.e add b1:b100 only if a1:a100 is >10000 <12000 =46 a b 10000 32 9000 64 12001 86 11999 14 9990 12 One way =SUMPRODUCT(--(A1:A100>10000),--(A1:A10000<12000),B1:B10000) Regards, Peo sjoblom boogie wrote: > Hi there > I am trying to create a formula that refences <> numbers in a columm then > adds up the from a different columm > i.e add b1:b1...

Repeating formula in Excel
Hi. I need to copy a number into 500 rows, with each number increasing by 1 in value. These numbers are used to track call numbers, so the format is like this: 2005-001 and I need it to repeat itself up to 2005-0550, and I don't want to spend the time typing each value in manually. Someone please help!!! One way: Delete the dash from your number. Select the cell with the number. Go to Format | Cells | Number. Choose Custom and type: 0000-000 click okay. Then, (still with the same cell selected) go to Edit | Fill | Series. Make sure the Step Value is 1. Change to Columns and put a s...

Replacing formula to cells EXTREMELY slow
Hi! I have the following code ------------------------------------------------------------------------------ Private Sub ToggleButton3_Click() Dim i As Long Dim Lastrow As Long Application.ScreenUpdating = False Lastrow = 1000 If ToggleButton3.Value = True Then 'Fill in the checking formula For i = 0 To 5 Cells(4, i + 9).Formula "=IF($A4="""","""",COUNTIF(INDEX('Check'!4:4,1,$AI$2):INDEX('Check'!4:4,1,$AI$3), & i & "))" Next Else For i = 0 To 5 Cells(4, i + 9).Formula "=IF($A4="""",...

Formulas dont work
I am trying to do a vlookup function with data that is imported and calculated from another cell, and it shows a result of #na. c3 is formated to custom "m" to show the number of the month from cell b3. b3 is "October-04" c3 shows "10" d3 "job name" b100 "10" a100 =vlookup(b100,c3:f20,2,false) result is "#na" If I enter "10" into cell c3 with format set to general, then the result shows the correct answer. Is there a way to correct this? -- comotoman -----------------------------------------------------------------...

Automatic Pareto data with Formula
I want to create a data table for use to create an Automatically updated Pareto Chart, for this I need that the values automatically be sorted in descending order, so I have the following example scenario: Column A has Labels and Column B has Values A -1 B -2 C - 3 D - 2 E -1 If I use the following formula: =INDEX($A$1:$A$5,MATCH(LARGE($B$1:$B$5,2),$B$1:$B$5,0)) I get B, but there are two 2 and if I use the 3rd largest: =INDEX($A$1:$A$5,MATCH(LARGE($B$1:$B$5,3),$B$1:$B$5,0)) I also Get B instead of the required D. How can I get it so the end results looks like this: C ...

how do you do you prevent outlook from sending copies
In Outlook '03, how do you do you prevent outlook from sending copies of the same email to individuals that appear on multiple distribution lists? Rules seem to hold a promising key, but the books i've consulted - Cardoza's "Using Microsoft office 2003" and Outlook for Dummies don't seem to indicate any solutions to this problem. Any feedback would be helpful, thanks! Crisra13 <cris.ramon@gmail.com> wrote: > In Outlook '03, how do you do you prevent outlook from sending copies > of the same email to individuals that appear on multiple distribution...

Display Formula Problem
All, I have search Microsoft's Knowledge Base, but could not find an answer. Hopefully, someone here can solve my problem. I am using Excel 2002. I have several project managers maintaining budgets in Excel workbooks. I am creating a summary Excel workbook. To get the data out of the workbooks, I start with entering an equal sign, click on the cell in one of the other workbooks, and press enter. The value from the source workbook is displayed correctly. Since I want to capture several columns and rows around this first cell, I want to copy formula in the first cell into near cells, I mu...

percentage formula #2
What is the formula for using one cell to figure a percentage of another cell. Example: colume A colume B 20000 13% of colume A Thanks One way: A1: 20000 B1: =A1*13% ==> 2600 In article <093f01c3861f$4a6902d0$a301280a@phx.gbl>, "Shawn S." <shawn3731@yahoo.com> wrote: > What is the formula for using one cell to figure a > percentage of another cell. Example: > colume A colume B > 20000 13% of colume A > Thanks ...

Formula using hours
How can I create a formula that will say: If this cell is higher than 9:01 but but lower than 9:04 show a number? Thanks for your help! Hi Ali, =IF(AND(A1>TIME(9,1,0),A1<TIME(9,4,0),17,"") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ali" <anonymous@discussions.microsoft.com> wrote in message news:731101c3e72a$ceb69220$a501280a@phx.gbl... > How can I create a formula that will say: > > If this cell is higher than 9:01 but but lower than 9:04 > show a ...

Excel Formula #21
Thankyou McGimpsey for your quick reply appreciated, and the frormula also works -- SAMSON ...

Copying WBS and Resource Names to New Project
I would like to copy just the WBS and Task Names from an existing project to a new project in Project 2003 Standard, nothing else. If I simply copy and paste the WBS and Task Names, I loose the connection between the WBS and Task Name such that all indentations in the Task list are lost, i.e. there is no longer any structure to the Task list such as summary and sub tasks. Paste special at least seems to maintain the WBS list, but the indenting in the Task list is still lost. How can this structure be preserved in the process of copying? Try inserting the Outline Level field a...

Bootable copy of XP partition.
Regards to All, Win XP SP2 I have two hard drives, each partitioned. The first drive has the XP partition (C) and a partition (E) with all my data. One partition on my second drive (D) has backup and archive files. The others are unused. I want to make a bootable copy of my c drive onto the first (unused) partition of the second drive without destroying the archive and backup partition (D). What program can do this? Acronis cannot do this and I have been assured that EASEUS Data Backup & Recovery cannot do it either. (Both of these want to destroy the whole ...

Advanced Vlookup Formula
Hello, I have a list of unique values in Column A in sheet 1. In sheet2, I am have data laid out in a table with row headers and column headers. I am trying to come up with a formula that will display the value that intersects in sheet 2, based on the combined column header and row header. So for example. Sheet 2 Column Headers Row Headers State City Weight Jeff TN Nashville 200 Tim FL Miami 155 Eric GA Atl 225 Sheet 1...

Nested formula (I think?)
I nedd a formula that says " if the value in cell a1 is between th values of cell x5 and y5, then do this, if not do this" I think it should start =if(between( .... but I'm lost after that. Barr -- Message posted from http://www.ExcelForum.com =IF(AND(A1>=X5,<=Y5),"do this","if not do this") you might want to remove the equal sign depending if you meant to include the values in X5 and Y5, my formula includes them while if you remove the equal signs it will exclude them -- Regards, Peo Sjoblom (No private emails please, for everyone's benef...

is there a way to format all cells that are formulas a color at o.
I have a spreadsheet that has formulas and numbers in it. I want to color code all of the cells that have formulas but I do not want to have to copy the action over and over. Is there a way to do the whole spreadsheet at once? Hi Priceie, Try: Edit | GoTo | Special | check Formulas | Ok | Apply desired formatting --- Regards, Norman "priceie" <priceie@discussions.microsoft.com> wrote in message news:236370A6-8906-4091-AB2C-CAEBFBB9FE28@microsoft.com... >I have a spreadsheet that has formulas and numbers in it. I want to color > code all of the cells that hav...

copying of CString to char*
hi, i have used the following code to perform some file copy CString strWork="d:\\work"; char *pFrom = new char[strWork.GetLength()+7]; pFrom = (char*)(LPCTSTR)strWork; strcat(pFrom,"\\*.*"); but when after the above steps the value of strWork also changes to "d:\work\*.*". but i want to retain the old value of strWork ie "d:\work". can anyone tell me why this happens? thanx anu. hi, thanks. its working now. as you said i also tried it in the other way using cstring directly but got the error message. "cannot copy file (1026)". bye...

Copying files w/ lots of links
I have created a large spreadsheet with tons of blueprints linked to it. Now I want to send this file to my customer. The file is over 10 megs so I burned a copy. My problem is I have all the files on the disc, but the links don't work . Are the links looking for the source on my hard drive? How can I get the links to respond to the files that are on the customer disc. I can jus redo the links if that's what it takes. Regards, Are you or your customer trying to work directly *from the disc*? XL likes hard drives. Try copying, or telling your customer to copy the disc to the hard d...

Trend Formula
What does the symbol E stand for in excel graphical trend formula? Is it 10, or exponential e? When I try to test the formulae I get the correct x=0 point but others are wrong; either way. I am using an x raised to the power 5 polynomial A number such as 1.234E-3 is to be read as 1.234�10^(-3) or 0.001234 Copying the trendline equation values can give terrible results unless you first format the equation for maximum precision Better yet use LINEST to but values into cells that you can reference to generate the forecast values See http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm ...

help with formula #9
G12 has a dropdown menu in it, Y for YES, N for NO. I12 is where the answers will go. So if I pick N from the dropdown box in G12 , I need it to show a answer of 0 in I12. If I choose Y in cell G12 , I need it to use this formula =Roundup(E12/300,0) so that it will give me a answer in cell I12. How to I write this and get it to work? Place in I12: =IF(G12="Y",ROUNDUP(E12/300,0),0) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Art" wrote: > G12 has a dropdown menu in it, Y for YES, N for NO. > I12 is where the answers will go. > So if ...