Pasting without updating cell references in formulas

I am a relatively low end user of Excel.  I need to copy a large section of
formulas to create a new section of my spreadsheet.  Excel does an excellent
job of changing all cell reference in the formulas based on the number of
cells that you cover in your copy and paste.  HOWEVER, in this case I want
almost all cell references to remain the same.  I can then edit each one and
change the one that I want changed.  Is there any easy way to do this.  I'm
sure there is.

Thanks in advance
Reg


0
rwillems (6)
10/8/2003 3:10:41 AM
excel 39879 articles. 2 followers. Follow

2 Replies
636 Views

Similar Articles

[PageSpeed] 52

As long as you are moving to the same sheet, just highlight the cells you
want to move and using mouse, click and drag on the border to new location.
All cell references remain the same.


"Reg Willems" <rwillems@sasktel.net> wrote in message
news:vo6vqtchugp8ce@corp.supernews.com...
> I am a relatively low end user of Excel.  I need to copy a large section
of
> formulas to create a new section of my spreadsheet.  Excel does an
excellent
> job of changing all cell reference in the formulas based on the number of
> cells that you cover in your copy and paste.  HOWEVER, in this case I want
> almost all cell references to remain the same.  I can then edit each one
and
> change the one that I want changed.  Is there any easy way to do this.
I'm
> sure there is.
>
> Thanks in advance
> Reg
>
>


0
dhymel (16)
10/8/2003 3:26:46 AM
Hey, Dale, that works just great!!!! and how much simpler can you get.
Thanks.  I just learned something new and have wracked my brains for a long
time to get Excel to do this.


0
rwillems (6)
10/8/2003 4:03:26 AM
Reply:

Similar Artilces:

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...

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...

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 ...

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...

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 ...

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...

Hong Kong Stock
Does Money support price update of Hong Kong Stock? Also, when will Money include Hong Kong Banking Institute? Hong Kong is not listed on the exchanges that have downloads from MSN Money - Belgium, Japan, US, Great Britain, France, Spain, Canada, Germany, Italy, Australia, Sweden, Netherlands are the ones supported. -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or http:/...

query to update Trans #
Hi all, I have a Claims system where there are multiple transactions per claim. Each of these transactions has a Transaction ID. Each Transaction now needs a Transaction # per claim. Going forward this is not a problem but is there an easy way to go back and update the TransactionNumber field? I have a DateTime Stamp (DateEntered) to let me know the order of the transactions. So for example i could have 100 transactions which will have Transaction IDs of 1-100 but now they need a Transaction # per Claim. If a Claim has 10 Transactions they should be numbered 1-10 and if another claim has 8 ...

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 ...

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...

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...

update pivot table
I have a sheet of info. which used to create the pivot table. I need to update this sheet every week. How can I set so every time when I update the sheet, the pivot table will be automatically update. Your help is very much appreciated. If anyone would please show me how to look in the archive, that would be great too. Mia If you're adding new rows of data to the source every week, you can use a dynamic formula to name the data source range, and use that as the source for the PivotTable. There are some instructions here: http://www.contextures.com/xlPivot01.html#Dynamic To ...

Cannot release cell
As soon as I open Excel and click into the first cell, the program will not release the cell. It will not even let me access the Menus above. Is this a software issue? I believe there is a quick keyboard sequence fix but I dont know what it is and can not find the information anywhere (including MS Excel TroubleShoot KnowledgeBase) Do you see EXT in the statusbar (bottom right corner)? If yes, hit the F8 key to toggle this off. Have you tried a different mouse or mouse driver? MEvans wrote: > > As soon as I open Excel and click into the first cell, the program will not > ...

update query based on date difference
I have a multuser database for salespeople i want to be able to remove the contact from their list if they do not get them to buy within 60 days. I have a date created field, and a sale date field the sales date is a continious form i need to only take the most recent date. I also have a yes/no box to send the contact to a lead box where i can give it to someone else. I have the moving the lead part down just not sure how to use the date fields to make this happen automaticly -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200703/1 s...

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...

Can I create an Excel chart from non-contiguous data cells?
Using Excel Office 2003, I'm being asked to create charts from existing worksheets, often several within a workbook, using specific cells (total/average cells). The cells are not adjacent to one another, but they are identical from one worksheet to another. In my case, the cells are K15, K29, K42, W15, W29, and W42. I'd like a separate chart for each worksheet, and would like to have the chart automatically created once the data is in the worksheet. Is this possible? You can select discontiguous data by selecting one area, then holding CTRL while selecting additional areas....

Error updating modified forms and reports
I get this error message when updating modified forms and reports error from Great Plains 8 to Great Plains 9. Unhandled script exception: Index 31 of array '[Not Found]' is out of range in script 'Launch_GetInfo'. Script terminated. Why/how to fix it? Thank You On Feb 24, 5:43 am, clairvoyant316 <clairvoyant...@discussions.microsoft.com> wrote: > I get this error message when updating modified forms and reports error from > Great Plains 8 to Great Plains 9. > > Unhandledscriptexception:Index31 ofarray'[NotFound]' isoutofrangeinscript'Launch...

ActiveSync does not sync new mail without intervention
I have a SBS2003 with Exchange 2003 SP2. The computer is behind a Watchguard firewall. ISA is not running. OWA & OMA both work fine via https from either mobile devices or computers. From Windows Mobile 5 works fine if in cradle (I'm assuming that activesync notes & tasks syncing keeps outlook open) but if remote Initial sync - works fine Send mail - works fine read mail messages - works fine sync'd message deleted - works fine new mail - only works if mail already read Activesync shows that it has synchronized all items without error but new messages do not sync! If a ...

Paste and Paste Special
What are the differences between paste and paste special and are these two functions the same for XP software from different sources? e.g Word, Publisher, Ventura and Word Perfect? -- _ _________________________________________ / \._._ |_ _ _ /' Orpheus Internet Services \_/| |_)| |(/_|_|_> / 'Internet for Everyone' _______ | ___________./ http://www.orpheusinternet.co.uk ...