pasting or moving formula cells without updating formulas

I have a flat spreadsheet with a results page at the end. The results page 
contains a set of formulae which refer to various cell locations within the 
body of the spreadsheet in order to return statistical results based on the 
values in said cells. Now I'd like to add more data to my spreadsheet, so i 
need to make it bigger; however, when I copy and paste, or select and drag 
the cells containing the formulae, Excel updates the formulae so that they 
refer to different cells which bear the same spatial relationship to the 
formulae as the original referees did before the formulae were moved. 
Normally this is a very useful function, but in this case it's a royal pain 
in the arse. If anyone could tell me how to move my formulae and still have 
them refer to the same cells I'd be most grateful and probably keep a little 
of my hair a little longer.

Thanks,

Jake
0
Jake (94)
4/12/2005 7:32:12 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
826 Views

Similar Articles

[PageSpeed] 54

"jake" <jake@discussions.microsoft.com> wrote in message
news:F32EF710-500B-42F4-861B-7A4A3159C0DE@microsoft.com...
> I have a flat spreadsheet with a results page at the end. The results page
> contains a set of formulae which refer to various cell locations within
the
> body of the spreadsheet in order to return statistical results based on
the
> values in said cells. Now I'd like to add more data to my spreadsheet, so
i
> need to make it bigger; however, when I copy and paste, or select and drag
> the cells containing the formulae, Excel updates the formulae so that they
> refer to different cells which bear the same spatial relationship to the
> formulae as the original referees did before the formulae were moved.
> Normally this is a very useful function, but in this case it's a royal
pain
> in the arse. If anyone could tell me how to move my formulae and still
have
> them refer to the same cells I'd be most grateful and probably keep a
little
> of my hair a little longer.
>
> Thanks,
>
> Jake

Snipped from http://www.mrexcel.com/tip048.shtml

a.. $A1 tells Excel you always want to refer to column A.
a.. (If you dragfill downwards, you get $A2)
a.. B$1 tells Excel you always want to refer to row 1.
a.. (If you dragfill to the right, you get C$1)
a.. $B$1 tells Excel you always want to refer to cell B1.

/Fredrik




0
4/12/2005 8:11:18 PM
Reply:

Similar Artilces:

IF THEN FORMULA #3
IF I HAVE THE OPTION TO PUT ANY OF THE FOLLOWING NAME IN CELL A1 TRACTOR ( REFERS TO $80) BOBCAT( REFERS TO $50) LABOR (REFERS TO $28) AND I HAVE THE FORMULA =SUM(A1*B2) IN CELL C1 HOW CAN I REWRITE THIS FROMULA TO DISTINGUISH BETWEEN TRACTOR, BOBCAT AND LABOR? PLEASE HELP ME =((A1="TRACTOR")*80+(A1="BOBCAT")*50+(A1="LABOR")*28)*B1 -- HTH Bob Phillips "ELISA@RELIABLECONTRACTORS.BIZ" <ELISARELIABLECONTRACTORSBIZ@discussions.microsoft.com> wrote in message news:1185FF6C-3C98-4AA8-907E-AA3001C207E0@microsoft.com... > IF I HAVE THE OP...

Counting spefic cell (not a range) with a value greater than 1
I am looking for a formula to count specific cells and a range (A5, A10, A15, A20 and so on) but only count when the value is greater than $1.00. So if the value of cell A5 was $1.50, the value of cell A10 was $1.25, the value of cell A15 was $0.50 and the value of cell A20 was $2.00 the total count would be 3. I'm stumped! =SUMPRODUCT(--(LARGE((A5,A10,A15,A20,A25),ROW(INDIRECT("1:"&COUNT(A5,A10,A15,A20,A25))))>1)) however if you want to sum every 5th cell greater than 1 you can use =SUMPRODUCT(--(MOD(ROW(A5:A50),5)=0),--(A5:A50>1)) -- Regards, Peo Sjoblo...

Move message to storage folder > CRASH
Most of the time I right click an email in my Inbox and select Move to Folder, Windows Live Mail (desktop app), crashes when I click OK to execute the move. When I restart WLM my Inbox still shows the message entry, although there is no message body. The folder where I directed the message to move contains a copy of the email (including message body) and the From entry appears within quote marks. The quotes do not appear in the opened message, only on the storage folder's message list. Any idea why this is crashing so often, or how to stop this behavior. This is frustra...

Moving spreadsheet rows with photos Problems in Excel 2007
I have an inventory worksheet in Excel 2002. There is a photo in one cell in each row that needs to move with the row. I formatted each photo to move and size with cell, and had no problems. Have just had to change to Excel 2007. I can move the row by cut and insert cut cells, and the photo moves with it. BUT the problem comes with the rows above the cut cells - the photos move down into the blank row left by the cut row when moving to another sheet- so the photos left are not with their original rows and descriptions. I have looked at all the options that I can find (steep learning...

concatenate will not paste special
Hi I am trying to concatenate first name and last name columns, all is good till I copy and paste special.....It say copy and paste area is not the same size...and it is. Here is the real dilemma, when I copy and past the data into a new spread sheet and concatenate it works, except in certain records it drops the actual name and I end up with a blank field, that causes great grief when I send said data to another process that strips blank lines and shifts remaining data up resulting in an absolute train wreck of srong names to addresses. Help If desired, send your file ...

Trying to split up info in one cell to several
I am working on a home PC, Excel 2003. I have a column (it is column C of A thru K, if that matters) of data that includes street address (may include suite number, etc.), city, state, zip and all info is separated by commas except state and zip. I am trying to separate the information in this column so that each part is in its own column. I need to keep all of the address (street number, street name, PO box number, suite number, etc. together) in one field, city in the next field, then state, then zip. The problem I am having when converting text to columns using the comma as the d...

My object-menu is gone when I moved my DB
When I moved my database onto another computer, the left object-menu disappeared and I can't edit the Database structure or reports anymore. Moving it back doesn't help. Is it because of the various Access versions? First Access Version: 11.6566.8132 SP2 Second Access Version: 11.5614.5606 What do I do? ...

copy n past
I am going to have some poems published, how can I send them to the publisher, he cannot open them as they have been writen using 2003, I have tried copy and paste into notpad, but it does not work, they cannot open my publication. katiemouse <g''day@australia.com> wrote: >I am going to have some poems published, how can I send them to the >publisher, he cannot open them as they have been writen using 2003, I have >tried copy and paste into notpad, but it does not work, they cannot open my >publication. Convert to PDF In addition to Uncle's fine advice. ...

Update only the added invitees
I recently started using Outlook 2007. I added an invitee to a meeting and wanted only them to get the invite versus the whole group. I checked the send to only the added invitees button. What went wrong. Is there a save and close option like 2003? ...

Using Quotes in Formulas
Hello everyone, Basic question: Is there a set quideline on when to use quotes in formulas and array formulas? -- Thank you... Elaine Hi! Can you be more specific? You must use quotes when referencing text values: =IF(A1="Red","yes it is","no it isn't") =SUMIF(A1:A10,"Ten",B1:B10) Do not use quotes when referencing numbers: =IF(A1=10,10,0) =SUMIF(A1:A10,10,B1:B10) Biff "Elaine" <Elaine@discussions.microsoft.com> wrote in message news:5879DD53-B927-401E-8304-3D158C51B232@microsoft.com... > Hello everyone, > > Ba...

VLOOKUP to return Cell Address
I have a table, and I want to do a Vlookup on Column1, and when I find what I am looking for, I want to sum column 2 from that row down to the bottom of the table. For example, Item 1 2345 Item 2 3478 Item 3 1298 Item 4 123 Item 5 1233 I want to find "Item 3" and then I want to be able to return the sum of (1298+123+1233). How would I do this? My thoughts were that I would do a lookup to get the row numner of "Item 3" and then do a sum() with the cell range starting from the row where I found "Item 3" but I'm not sur...

Reflect cell contents into another worksheet #2
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 ~ hi on sheet 2 in your specific cell put this =sheet1!A1 regards FSt1 "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 not...

Copying image to cell
Hi. I am using Excel 2002 (XP). I would like to accomplish the following for some cells. If the cell is below a certain value, the cell value is CIRCLED in green. If the cell value is above a certain value, the cell value is BOXED in red. I can achieve the boxing in red by using conditional formatting of the cell borders, but I cannot figure out how to accomplish the circle. Does anyone have any ideas? Thanks, Mike. One way: put this in your worksheet code module: Private Sub Worksheet_Calculate() Const CERTAIN_VALUE = 100 Dim cell As Range Dim shp As ...

At each change in data apply formula
Is there a function/ code that can look down a column and apply a formula when there is a change in data. i know there is the subtotal function but this does not work for my requirement. Using the following data I would want to automatically look at where the data changes (in column A) and apply a formula in another column (say Column B) for the same row. I would therefore expect to apply the formula as per the following example to cells B1, B4, B5 and B9 Col A row 1 AAA row 2 AAA row 3 AAA row4 BBB row5 CCC row6 CCC ro...

Updating multiple linked workbooks
We have several workbooks which 1 gets data from other etc. is there anyway to have a one step to update all at once Not in the usual sense. Generally you can only update from one 'level' of linked workbooks unless all are open. What you can do is open all related workbooks and then use File | Save Workspace to quickly have access to all of those needed workbooks. See the Help topic on Workspace for more information. "dk" wrote: > We have several workbooks which 1 gets data from other etc. is there anyway > to have a one step to update all at once ...

Quick Formula Question
Dear Excel 2003 users, I have a user in the field that likes to start formulas with a plus sign (becasue she uses a numeric keypad without an equal sign). Sometimes the formulas work, sometimes they do not. Here is an example: she enters +150/2 she could get: =150/2 or 75 Why does Excel sometimes run an immediate result on the entry and sometimes convert it to a proper formula (which is the desired result)? Thanks! Kevin Any chance she's hitting F9 after entering the formula (but before the enter key)? Or is there any chance she has a helpful macro that's jumping in where i...

Making legend (or textbox) move as data changes
Hi I'm trying to make an XY scatter chart which my users will be able to change, just by changing the data in the data ranges. The chart contains several curves each having its own legend/text box, placed at the right end point of the curve. When the data changes, I need the legend/textbox to move along with the end point of the curve (colors and different line styles are not an option). I have tried to group the curve with the legend/textbox but it doesn't seem to work. Any ideas? mr Hi, Maybe these examples will help. http://peltiertech.com/Excel/Charts/LabelLastPoint.html h...

Recipient policies don't update mailbox addresses.
Hello, We have a W2K AD, E2K and a third-party mailsystem. The E2K server is not authoritive for our e-mail domain and all the AD accounts which are not mailbox-enabled are mail-enabled. My company has a new email domain and my problem is that I can't get the Recipient Policies to stamp the new address on the mailbox-enabled users. Our E2K server has two Recipient Policies: - the Default policy, filtering on (mailnickname=*), with a primary SMTP address %g.%s@localhost and a secondary smtp address %g.%s@ourdomain.com, with Lowest priority; - a Custom Policy, filtering on (&(mailNick...

Cell contents?
Is it possible for a cell to contain a numerical value, which is used for various calculations, and an alphabetical designator that prints but does not affect calculations? Example: If cell A1 contains an entry of 6, and cell A2 contains an entry of 6z, is there a way for the addition of the two cells to show 12, or their multiplication to show 36, yet still display 6z in cell A2 when the sheet is printed? Thanks. -- ---------- CWLee Former slayer of dragons; practice now limited to sacred cows. Believing we should hire for quality, not quotas, and promote for performance, not preference...

Count consecutive cells
I have a table which tracks the attendance at an event over the years. Each time someone attends, I enter 1 in the column for that year, otherwise blank. I'd like to know the most consecutive events people have attended. So, if I have rows like (with 'b' representing a blank cell): Name1 b 1 1 1 b 1 1 1 1 b 1 b 1, most consecutive = 4 Name2 1 1 b b 1 b b b b 1 b 1 1, most consecutive = 2 Name3 1 1 1 1 1 1 b 1 1 1 1 b 1, most consecutive = 6 Name4 1 1 1 1 1 1 1 1 1 b 1 1 1, most consecutive = 9 Name5 b b b b b b 1 b 1 b 1 b b, most consecutive = 1 Is there a formula I can use to c...

Remove Spacing In A Cell
I am trying to eliminate spacing for a upc in a cell. For a example: 0 11111 22222 3 011111222223 I have tried Clean, Trim, & Substitute. Anyone have any answers or Ideas? Thanks, Hi Span, See David McRitchie's TrimAll page at: http://www.mvps.org/dmcritchie/excel/join.htm#trimall --- Regards, Norman "Span" <Span@discussions.microsoft.com> wrote in message news:1343031C-1E8E-4CA8-8B51-B353B734BAE6@microsoft.com... >I am trying to eliminate spacing for a upc in a cell. For a example: > 0 11111 22222 3 > 011111222223 > > I have tried Cl...

How do extract cells from hundreds of excel files and put in one n
I need to extract expense totals from employee expense reports and put in to one file for import then in to the payroll system fo reimbursement. Lots of unanswered, but needed information with this question. Generally this can be done relatively easily, with a big "IF" - and that IF is whether all of the files you'd be examining (the employee expense reports) have the same format, content and layout. Get in touch with me via email through (remove spaces) Help From @ jlatham site.com and we can gather some more information and probably have a solution in your han...

Inserting text within a cell
I would like to insert a , with a cell. i have Wood T 3,1 i would like to have Wood T,3/1 Sorry it should read I have Woods T 3/1 i want Woods T, 3/1 Thank you "Hayley" wrote: > I would like to insert a , with a cell. > > i have Wood T 3,1 > > i would like to have > Wood T,3/1 If cell A1 contains (surname)(single space)(single initial)(single space)(score), the following will achieve what you want: =LEFT(A1,FIND(" ",A1)+1)&","&RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND(" ",A1)+1))) HTH "Hayley" wrote: >...

Insert a Formula in a TextBox
Dear all, I would like to insert a formula in my TextBox in the moment I initialize my UserForm: Private Sub UserForm_Initialize() With cboDivision .AddItem "HE" .AddItem "IT" .AddItem "IC" .AddItem "CO" End With txtDate.Text =3D FormulaR1C1 =3D _ "=3DYEAR(TODAY())&IF(LEN(MONTH(TODAY()))=3D1,0&MONTH(TODAY()),MONTH (TODAY()))&IF(LEN(DAY(TODAY()))=3D1,0&DAY(TODAY()),DAY(TODAY()))" End Sub Is it possible? Thanks in advance! Andr=E9 txtDate.Text = Format(Date, "yyyymmdd") -- _____________...

Automatic Disable of Autoformat in Excel without selecting range of cells
Hi, Please help on how to automatically disable autoformat in excel without specifying the range of cells that do not need formatting. Thanks. Do you want Excel to stop copying the formatting from rows above? If so, you can do the following: 1. Choose Tools>Options. 2. On the Edit tab, remove the check mark from 'Extend list formats and formulas' 3. Click OK Iris wrote: > Hi, > > Please help on how to automatically disable autoformat in > excel without specifying the range of cells that do not > need formatting. -- Debra Dalgleish Excel FAQ, Tips...