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
532 Views

Similar Articles

[PageSpeed] 39

"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:

locking formulas
Is there a way I can lock my formulas so no one can change them when i send my spreadsheet out? I have about 50 cells with formulas in them in 4 or 5 columns and I need them all locked. Thank you for any suggestions. -- speary ------------------------------------------------------------------------ speary's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24959 View this thread: http://www.excelforum.com/showthread.php?threadid=389055 Select all cells that DO NOT require to be locked, go to Format>Cells>Protection and remove the check mark beside "L...

Moved email account still connecting to old
I moved some email accounts from a Exchange 03 server to a new 03 server and all but (1) moved without a problem. When I try to connect the users outlook it tells me it can not and than connects to the old email server. I have unchecked the use cache mode when creating and get the same error. Does anyone know how to reconnect this account to the associated user? Thanks How were you moving these mailboxes? On Mon, 28 Feb 2005 14:55:03 -0800, "Micah" <Micah@discussions.microsoft.com> wrote: >I moved some email accounts from a Exchange 03 server to a new 03 server ...

coping formula
I am trying to copy a formula down a column. When I copy the formula down I want the cell to be blank until i fill out the cells to give me my balance. My three columns are Debit / credit / balance. My formual reads : f7(balance column)+e8(credit column)-d8 (debit column) when i copy the formula down form my balance column it gives me the balance in every cell. I would like for it to be blank until i fill in my debits and credits. I hope you understand the way i wrote this thanks much -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200803/1 You ...

Formula: What am I Doing Wrong
4 A 3 A 2 B 1 A 4 $6.00 3 $6.00 2 $2.00 1 $6.00 In the first example I want to know how many instances of A there are in column B multiplied by the number in Column A. Answer would be 8. Formula: sumproduct((a1:a4)*(b1:b4="A")) But if I want to find out how many instances of $6.00 there are instead of A, I'm stumped. I get either 0, or 48. Must be something simple. It is better to use =SUMIF(B:B,"A",A:A) than sumproduct((a1:a4)*(b1:b4="A")) I assume you mean =COUTIF(A:A,6) -- HTH Bob (there's no email, no snai...

Paste special treats cells as a picture
I just got a new computer, and a newer version of excel, which is great, but whenever I try to copy from one file to another, my paste special function treats the copied cells as a picture. Instead of the normal paste special popup, where it asks me how I want the data to come out, I get a different popup that shows the source as a "Microsoft Office Excel Worksheet Object." It asks me what format I want the picture to come out as, and gives me a list of different file formats. If I try to just paste a link, it puts a picture of the other file on the new one. I talked to so...

Updating drivers
When I go to device manager I tried to do a driver update and all the devices say they are currently up to date when I search online. I only did this when I tried to different programs that I saw online to see if you have outdated drives. 2 different driver scans shows 20 out of 38 drivers are out of date and told me what they were. One was my Toshiba disk drive mk3252gsx , which the driver shows last driver install was 6/21/06. So I go to Intel and download the Intel Driver Update Utility. They tell me to go to Toshiba's website and they say go to Intel? Please help. Do I buy...

Copy and paste formula; Value pastes instead
Excel 2002 I've tried starting several worksheets, checking formats, pasting special (formula only). For some reason the formula actually copies OK by looking at the formula bar, but the value in the cell is identical to the value of the copied cell.Incorrect. Tried even the simplest formulas with no joy. BK ...

Will excel 2003 format a cell to the year?
It seems like it should be obvious, but I am finding it impossible to get excel to do a date format that recognizes a year. Is there some way? I typed a date in A1 (09/28/2006) and used format|Cells|number tab|custom category|yyyy with my USA settings. kateofmd@msn.com wrote: > > It seems like it should be obvious, but I am finding it impossible to > get excel to do a date format that recognizes a year. Is there some > way? -- Dave Peterson ...

Is there an Excel formula to round a date to the end of the month
User enters a specific date, I'd like to round that entry to the end of the month With a date in A1: =DATE(YEAR(A1),MONTH(A1)+1,0) step into the next month and then back one day. -- Gary''s Student - gsnu201003 "Siralec" wrote: > User enters a specific date, I'd like to round that entry to the end of the > month Another way... This requires the Analysis ToolPak add-in be installed for Excel versions prior to Excel 2007. A1 = some date =EOMONTH(A1,0) Format as Date -- Biff Microsoft Excel MVP "Siralec" &...

Conditional format
Hi Conditional format Is there a way to get a cell's format determined by the format of a different cell?! e.g. Could one get an entire row market up in say bold red if one cell in that row was say less than 100 With thanks Ship Shiperton Henethe Hi Shiperton yep ) select the rows that you want to apply the conditional formatting to, ensuring that the first row of this group is the first row at the top of the screen (it gets confused sometimes if it isn't) the following example assums you've selected from row 2 onwards and you want the row to turn red if ...

Formulas for birth year and age in user defined fields
I'm using outlook 2002. I want to create 2 user-defined fields in my contacts, but am having trouble figuring out how to do it: 1. Year of birth field, based on the value entered in the birthday field - I created a new user defined field, with the Type set as Formula, and the formula set as: Year ([Birthday]). The year is displayed, but formatted as a number (1,957) rather than a year (1957). How can I get the number to be displayed without the comma? 2. Age, based on difference between birthday and present date - Is there a simple formula to accomplish this? I haven't been ab...

Formula returned in vlookup function
I've used vlookup for a while now in the office 2003. Just converted to office 2007. Now when I do a vlookup I get the formula displayed in my current cell instead of a returned value. I can't figure our what I'm doing wrong. Can someone help? Thanks bmc You've probably got a tick in the wrong place: Try Office Button (top left hand corner) Excel Options (bottom line) Click on advanced Scroll down to Display options for this worksheet and untick 'Show formulas in cells instead of their calculated results Click OK Hope this helps "87vette" wrote...

all cells in 2 workbooks have automaticlly changed to date format
Why would excel 2007 automaticly change formats? Upon opening a workbook this morning all the blank cells in the 16 sheets had changed from general format to date format, and 99.9% of cells with data or formulas were also changed to the date format. I opened another workbook and it was find, I closed it and reopened it and the same thing occurred, most cells had been changed to the date format. The first wb containts payroll data, with mostly sumifs functions and was working fine yesturday. The second wb contains data using vlookup and if functions. Thank you Deb Found the...

Parent spreadsheet updating child spreadsheets
Hi all, I have a spreadsheet with a tab (called reference data) that contains budget information, it is used as a parent spreadsheet. All budget information is entered on this parent spreadsheet. Then there are 200+ child spreadsheets that use this information to perform calculations. Each child spreadsheet has a reference data tab as well, it is identical to the reference data tab on the parent spreadsheet. Here's the problem, in the course of the year the child spreadsheets are the ones that get updated. The parent spreadsheet gets updated in the spring of each year. Various...

Moving Outlook Express files to new computer using Outlook
What is the best way to get my Outlook express folders on to my new computer? I want to use Outlook instead of Outlook Express on my new computer. Any suggestions appreciated. This newsgroup is for support of Outlook 97, 98, 2000 & 2002 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=microsoft.public.windows.inetexplorer...

Text data
I am entering text data into a spreadsheet and I need to know how to calculate the number of times particular words/phrases appear in each column. I am very new to excel, so any help would be appreciated! =COUNTIF(A1:A1000,"word") replace A1:A1000 with your range and word with the word you want to count -- Regards, Peo Sjoblom "excelbeginner" <excelbeginner@discussions.microsoft.com> wrote in message news:FE55CF40-F3B6-42F3-918F-D200690758F6@microsoft.com... >I am entering text data into a spreadsheet and I need to know how to > calculate the nu...

"Paste Special" with the right click mouse
I would like to have the option of "Paste Special" with the right click mouse in all microsoft office suite. Thanks, ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=de847738-df56-4...

Clipboard pasting
I copy something to the clipboard using Publisher 2007 and the computer tells me it is in the clipboard; however, when I go to paste that information into another document, Publisher acts like it is not there. I then need to close the document I wish to paste it into, reopen it, and then, like magic, there is the information in the clipboard and I am able to paste it. UGH!!!!!! -- Ginger Christenson Open the Office Clipboard in Publisher. Edit, Office Clipboard. What program are you pasting to? If it is an Office program, open the Office Clipboard in that program too. -- Mary Sauer ...

Formula Bar
Have cells custom formatted for seconds :ss but in the formala bar shows and invalid date and time, is there a way to make the formula bar show the correct value of the cell i.e. typed in cell - 55 for 55 seconds but the formula bar showing content as: 1900-02-24 12:00:00 AM for me to get it to display as :55 have to enter it as :55 but then can't use it for a calculation if format it as mm:ss enter 55 shows as 00:00 in the cell but in the formula bar shows content as: 1900-02-24 12:00:00 AM need cells to display a seconds :55 but will need to use the cells for oth...

New update: synchronize with iCal doesn't work.
Finally it possible to synchronize Entourage with Apple Address Book and iCal. Address Book works, but the data from iCal do not appear in Entourage. Is someone else sucessful in this? -- To mail me: look closely to my address In order for your iCal entries to show up in Entourage, they have to be in the "Entourage" calendar in iCal. Entourage can only sync to one calendar, and that's iCal's new "Entourage" calendar; try assigning one of your entries in iCal to it, and you should see it show up in Entourage as well. On 16.03.06 4:48, in article 1hca2ko.19epc6u1...

Keep certain cells from moving
I'm sure this will be an easy one, but I can't seem to find the info i help. I'm using Excel 97 and have a simple sheet with a few columns o data. I've setup auto-filters for colums A-E. What I want to to is put some formulas in column F that don't ge blanked out when I use the filters (if they're on a line that doesn' meet the condition I've selected, they dissappear). I'd also like the to give me dynamic totals. For instance, I have dollar amounts attributed to certain people in on column and their names in another. If I use the filter to select o...

Copy/Paste loop
I have a form on one sheet in excel, on the next sheet i have mad different coloums with different headings. I want the data from th form to be copyed to each heading e.g. name in form is copyed to unde the coloum with title name. So each time a user fills in there details he/she clicks submit an there data is copyed to the next page, like a small database. Is there a macro that can do this as i keep getting different error each time. I want this to continue in a loop Please help!!:confused -- Message posted from http://www.ExcelForum.com This might get you started: Option Explicit Opti...

Pasting formulas advances cell reference
Hi When i paste special from one cell into another, it advances the cell reference and i just want an exact match i.e. A1 formula ='Feb 2010'!C18 but when i paste it into another cell it pastes it as ='Feb 2010'!D18 The C changes to D, how do i stop this happening? Thanks for any help in advance Derek Use absolute referencing. Below are the different reference styles. A1 Relative referencing. Both column and row will change if you copy or drag the formula. $A1 The column reference is fixed and will not change A$1 The row reference is fixed and wil...

When I copy a picture and paste it into Publisher it disappears
I have tried to copy a picture from my documents and paste it into Publisher and the picture disappears. What version of Publisher? What happens if you go to... Insert / Picture / From File? -- John Inzer "Cathy" <Cathy@discussions.microsoft.com> wrote in message news:EF2CA50C-78D9-47DB-8799-E9C3C9171038@microsoft.com... >I have tried to copy a picture from my documents and paste it into >Publisher > and the picture disappears. I am using Office Profession Edition 2003. I tried another picture and so far it is still there. Thanks. "John Inzer"...

Curly Brackets In Formulas
I'm trying to copy formulas from one spreadsheet to another similar to the one below, but the curly brackets disappear, rendering the formulas inoperable (in the original document, they compare values in one range of cells against another, count the cells that match as 1, and adds them up). Adding the curly brackets manually just converts the cells to text. How do I make these formulas work in the new document? I'm using Excel 2000. TIA. ~Charie G. {=SUM(COUNTIF(B5:F5,$B$2:$F$2))} Hi Charlie the {} indicate that the formula is an array formula which is entered in a cell using a c...