Keeping the Same Cell References

What's the easiest way of copying a cell with a formula in it and
pasting it into another cell with the exact same formula?

I also have ASAP utlities, and I couldn't figure out how to do it with
that either.

0
JohnP26 (33)
12/28/2007 7:10:56 PM
excel 39879 articles. 2 followers. Follow

7 Replies
546 Views

Similar Articles

[PageSpeed] 46

One way:

Remove the = sign before copying and re-insert it in the new location after pasting

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JP" <JohnP26@msn.com> wrote in message news:qfian3pttvr0ju1eddj97quhiqon28snfl@4ax.com...
| What's the easiest way of copying a cell with a formula in it and
| pasting it into another cell with the exact same formula?
|
| I also have ASAP utlities, and I couldn't figure out how to do it with
| that either.
| 


0
nicolaus (2022)
12/28/2007 7:47:57 PM
Manually.............just preface the formula with an apostrohe.

Copy and paste then remove the apostrophe from both spots.

For another manual method of copying multiple formula cells see John
Walkenbach's site.

http://www.j-walk.com/ss/excel/usertips/tip066.htm

By VBA macro for a single cell.

Sub CopyFormula()
Dim X As New DataObject
Dim CelCopyTo As Range
    X.SetText ActiveCell.Formula
    X.PutInClipboard
    On Error GoTo endit
    Set CelCopyTo = Application.InputBox( _
            prompt:="Select the CELL" _
            & "to which you wish to paste", _
            Title:="Copy Cell Formula", Type:=8).Cells(1, 1)
    X.GetFromClipboard
    CelCopyTo.Formula = X.GetText
endit:
End Sub



Gord Dibben  MS Excel MVP


On Fri, 28 Dec 2007 11:10:56 -0800, JP <JohnP26@msn.com> wrote:

>What's the easiest way of copying a cell with a formula in it and
>pasting it into another cell with the exact same formula?
>
>I also have ASAP utlities, and I couldn't figure out how to do it with
>that either.

0
Gord
12/28/2007 7:51:30 PM
Highlight the formula in the formula bar.

Either right click>Copy or Edit>Copy. Hit escape.

Then paste the formula wherever you want.

Of course, this doesn't help if you want to copy a whole block of formulas.

-- 
Biff
Microsoft Excel MVP


"JP" <JohnP26@msn.com> wrote in message 
news:qfian3pttvr0ju1eddj97quhiqon28snfl@4ax.com...
> What's the easiest way of copying a cell with a formula in it and
> pasting it into another cell with the exact same formula?
>
> I also have ASAP utlities, and I couldn't figure out how to do it with
> that either.
> 


0
biffinpitt (3172)
12/28/2007 7:56:43 PM
Yes, that's easier!

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"T. Valko" <biffinpitt@comcast.net> wrote in message news:%237hMFvYSIHA.5264@TK2MSFTNGP02.phx.gbl...
| Highlight the formula in the formula bar.
|
| Either right click>Copy or Edit>Copy. Hit escape.
|
| Then paste the formula wherever you want.
|
| Of course, this doesn't help if you want to copy a whole block of formulas.
|
| -- 
| Biff
| Microsoft Excel MVP
|
|
| "JP" <JohnP26@msn.com> wrote in message
| news:qfian3pttvr0ju1eddj97quhiqon28snfl@4ax.com...
| > What's the easiest way of copying a cell with a formula in it and
| > pasting it into another cell with the exact same formula?
| >
| > I also have ASAP utlities, and I couldn't figure out how to do it with
| > that either.
| >
|
| 


0
nicolaus (2022)
12/28/2007 8:03:16 PM
If you have downloaded the asap exe file, click on it (best when excel is 
not open), and follow the instructions. There are step by step instructions 
on the site.
http://www.asap-utilities.com/installation-step-by-step.php
You will have a new menu item in excel, called ASAP utilities. In this are 
many things which are not available, at least with a couple of clicks, in 
excel normally.
I use the convert numbers to text, creating an index sheet, and several 
others regularly.
In the ASAP utilities in your excel menu is a link to the user guide (in pdf 
format).
Hope that helps,
Barbara


"JP" <JohnP26@msn.com> wrote in message 
news:qfian3pttvr0ju1eddj97quhiqon28snfl@4ax.com...
> What's the easiest way of copying a cell with a formula in it and
> pasting it into another cell with the exact same formula?
>
> I also have ASAP utlities, and I couldn't figure out how to do it with
> that either.
> 


0
12/28/2007 10:37:22 PM
After placing the cell in edit mode (F2, etc.), you can highlight the 
formula in the cell or in the formula bar, copy, press escape and paste 
where you want. Excel will not alter the relative addresses.


"JP" <JohnP26@msn.com> wrote in message 
news:qfian3pttvr0ju1eddj97quhiqon28snfl@4ax.com...
> What's the easiest way of copying a cell with a formula in it and
> pasting it into another cell with the exact same formula?
>
> I also have ASAP utlities, and I couldn't figure out how to do it with
> that either.
> 


0
Tyro (331)
12/28/2007 11:33:49 PM
Thanks everyone for all the good ideas!

On Fri, 28 Dec 2007 15:33:49 -0800, "Tyro" <Tyro@hotmail.com> wrote:

>After placing the cell in edit mode (F2, etc.), you can highlight the 
>formula in the cell or in the formula bar, copy, press escape and paste 
>where you want. Excel will not alter the relative addresses.
>
>
>"JP" <JohnP26@msn.com> wrote in message 
>news:qfian3pttvr0ju1eddj97quhiqon28snfl@4ax.com...
>> What's the easiest way of copying a cell with a formula in it and
>> pasting it into another cell with the exact same formula?
>>
>> I also have ASAP utlities, and I couldn't figure out how to do it with
>> that either.
>> 
>

0
JohnP26 (33)
12/29/2007 4:15:55 AM
Reply:

Similar Artilces:

How do I add the last 10 cells
If I have a column labelled, say, temperature, that is often being added to, how do I add up the last 10 cells. I want excel to do it for me automatically. I want a formula that will find the last filled-in (non blank) cell in a column and then add up the 10 entries above that. Assuming your data starts in A10, then in A9 perhaps, try the following:- =SUM(OFFSET($A$10,COUNT(A10:A9995)-10,,10)) If your data started in A15, formula would be:- =SUM(OFFSET($A$15,COUNT(A15:A10000)-10,,10)) This assumes you have no other data below this range in that column (Or at least within the COUNT range)....

find cell value
hi i have this formula that identifies the last column with data in it. =ADDRESS(35,MATCH(6.022*10^23,33:33)) this works a treat what i would like to do is us the result of this and subtract 3 column of it ie ADDRESS(35,MATCH(6.022*10^23,33:33)) = N35 i would like to have a formula that takes N35 and sub tracts 3 columns fro it giving K33 i then intend making that my range ie k12:n33 and copy the data else where to work on thanks kevin If =ADDRESS(35,MATCH(6.022*10^23,33:33)) returns $N$35 then =ADDRESS(35,MATCH(6.022*10^23,33:33)-3) will return $K$35...

'sticky' cell selection
When I select a cell it cannot be deselected and if i move my mouse it selects all cells from the original. I cannot select a singular cell anywhere else on the worksheet or select any other commands. Thus the name sticky. How do I unstick my selection to carry on using the worksheet. yippeekiay, sounds like you are in Extended Mode, EXT on in the right hand corner or the status bay, press the F8 key to get out of it -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always apprecia...

Average & blank cells
Hi guy's, I have an issue with a sheet and an average formula: I am subtracting one date from another to achieve a result (no problems) i have used the following if statement: =IF(E5-D5=0,"",E5-D5) to not show zero values as there is a long list of dates and not all the list would be fillied in all the time. My problem is that i need to create an average for these figures but need to also include the zero's in the average formula that may have the same start and end date (which results in zero but not displayed due to my if statement) without including the zeros in the cell...

16 th digit of a number in a cell gets chopped to zero
I am trying to enter a 16 digit number, like a credit card number, and no matter what kind of formatting I try, the 16th digit of the nunmber gets reset to zero! Actually, this happens for the 16th digit and beyond as well. i.e, the 17th, 18th, etc all get reset to zero no matter what digit I entered. Which begs the question whether this is a bug or is there is a upper number limit that a cell can display? If its the latter, then thats pretty dumb, since all I wish to do is just display this number and not run any math operations on it! I tried formatting it as text, or using a speci...

Hide button based on a cell value
Hello, I have been trying to write a macro that would hide a button based on a cell value (cell N20). Somehow, it doesn't work. If N20=1, the button should be visible. If not, then it should be hidden. The button is called Button 4388 and the sheet is called Results. This is what I have come up with so far but as I said it doesn't work (please don't laught, I'm a beginner!): Sub HideButton() Dim myButton As Button With ActiveSheet Set myButton = .Buttons("Button 4388") If Range("N20").Value = "1" Then ..Visible = True E...

Merging Info in Two Cells #2
I did get a partial response, thanks Barb, but now I need to dig into VBA and I stumble. I guess the formatting needs to be done in VBA and the easiest way is by copying each cell and pasting the values (otherwise, I am not sure if I can do partial formatting of a cell, i.e. superscripting parts of it). So the code would read: Range("c5:g5").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False With ActiveCell.Characters(Start:=4, Length:=9).Font ...

Keeping user specified Opportunities Confidential
We have a client who by default wants an open security where all opportunities can be Read (not edited) by others. However, there are instances where an opportunity must be denoted as "Confidential" where no one except specified people may have access to this record(s). Any ideas on how I can accomplish this? The use of Teams is out because this by default All users can Read All Opportunities. -- Carroll Little Vis.align, LLC 610-692-3290 x3326 www.visalign.com There is a capability using the SDK to make records "Private" to the owner; such records can then be ...

Outlook keep on asking to enter username/password
In one of our PCs, outlook keep on asking to enter username/password. but after I entered the right username/password, this window popup again. so I created new profile in the outlook and even to another PC. but all the time, this user still have the same problem. So I guess that's the server-end problem. while I reset the password in the server, but it did not work. I have to clue about this problme now. is there any other way to troubleshoot this problem? thanks in advance Bruce cao What format are you using for the username? You might try the full email address as the username....

keep pivotchart formatting
Hi! Is it possible to keep all the formatting in a pivotchart after i refresh the data. Although i check the preserve formatting option, every time i refresh tha data, i loose all the changes i made in the chart, and it allways goes back to the default excel formatting. Thanks You may set the property "HasAutoFormat" to False. The code would be like the following: ActiveSheet.PivotTables("PivotTable1").HasAutoFormat = False. Regards. "Pmxgs" <pmxgs@netvisao.pt> wrote in message news:newscache$cl5lph$7ai$1@newsfront4.netvisao.pt... > Hi! > ...

PDF icon in calculated cells
I have a calculated column and a PDF icon has appeared. If I delete the contents of the cells, the PDF icon remains. How can I delete the PDF icon? Right click on the icon and select cut from the popup menu. -- Regards, Tom Ogilvy "Texas Bald Eagle" <Texas Bald Eagle@discussions.microsoft.com> wrote in message news:BEBE1E3D-762B-43DE-8A60-99A86F8A8A20@microsoft.com... > I have a calculated column and a PDF icon has appeared. If I delete the > contents of the cells, the PDF icon remains. How can I delete the PDF icon? ...

Can you change cell fonts using formula?
I have a formular: =REPT("|",(VLOOKUP($E$4,SummaryTable,6,FALSE)/200))& " " & VLOOKUP($E$4,SummaryTable,6,FALSE) but I need to change the font of the 2nd "VLOOKUP($E$4,SummaryTable,6,FALSE)" section. Is this possible to do? No. but you can change to text and do whatever is desired. However, you no longer have a formula. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Ayo" <Ayo@discussions.microsoft.com> wrote in message news:F48837E4-7014-421C-8958-FC40C5AB3C0E@microsoft.com... >I hav...

Flag row if cell values = something specific
Let's say A1 = top and B1 = Bottom in C1, I want to say that if A1 = top and B1 = bottom then the cell background color of C1 should be red. How can I do this? And I need to do this in a macro. I can't use conditional formating from the menu. I have a recorded macro that does all my formatting and I need to add this. "Some Dude" <sdatt@myplace.com> wrote in message news:e9kw7DrfGHA.4864@TK2MSFTNGP05.phx.gbl... > Let's say A1 = top > and B1 = Bottom > > in C1, I want to say that if A1 = top and B1 = bottom then the cell > background color of...

Using Indirect Cell References in a Chart
I am trying to create a 'self-sizing' chart, but don't know if I can use indirect cell references in a chart. Here's the scenario: I have a tab with data(DataTable! A2:A20) and a tab with Graphs (Graphs). I use a Max formula to determine the last row of data entered, and I've labled that formulas as 'DataTable!LastRow'. In my 'Graphs' tab, I have a cell called 'Graphs! XAxisLabel' with the formula ='Datatable!A3:A'&(DataTable! LastRow)' that displays the rows of data to be used in the chart. I want to use a formula (=Graph...

Delete cells with content that don't contain the =?UTF-8?B?wqMgc3ltYm9s?=
I have a very large messy excel file that contains some data I want to isolate. The other data is unneeded and basically in the way. I want to delete/clear all the other cells that do not contain the ? pound symbol. This way I will be left with just the pricing info I need. All help appreciated On Fri, 23 Mar 2012 12:46:11 GMT, Gary N <gary.neill@allstate.com> wrote: >I want to delete/clear all the other cells that do not contain the ? pound symbol. "Be careful what you wish for" This can be done with a VBA Macro: To enter this Macro (Sub), <alt-F11> opens t...

Enter date automatically in cell
Hi i would like my system date to be inputted into an excel cell, how do i go about doing this. Cheers NO Hi Mo CTRL-; will add the date in the activecell -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "MO" <anonymous@discussions.microsoft.com> wrote in message news:77a201c3e748$c4caea40$a601280a@phx.gbl... > Hi i would like my system date to be inputted into an > excel cell, how do i go about doing this. > > Cheers > > NO Enter =Today() in the cell >-----Original Message----- >Hi i would like my system date to b...

How to calculate (generate) a cell reference
I need a way to generate a cell address (row,column) from the value in another cell and be able to use the contents of that in a calculation. Here's my situation: I have a table of mileage readings for my car taken at odd intervals. Note that the "[Row]" column is the actual row number in the spreadsheet and not part of the data. It is included because I want to reference it later. [Row] Date Odom Miles Days Mos Yrs M/Dy M/Mo Mi/Yr 13 3/13/05 48,041 1,141 50 1.64 0.14 23 695 8,335 14 7/10/05 50,922 2,881 119 3.91 0.33 24 737 8,843 15 10/29/0...

hyperlink to a cell: adjusting for changes?
(Excel 2002) I recently discovered that Excel can hyperlink to another cell in the same workbook, which looks to be very useful. In this re, ... Using (row,col) designation, the link's 'reference' (using the word loosly) appears to be absolute: If I set it to 'A31', and then insert/delete row 29, the link will still be to 'A31' - as if I'd spec'd $A$31, in most other contexts. I know I can get around this by referencing a 'named' target cell. This is OK, ... but, a litte more effort. So, just to be sure I'm not missing someth...

Alert If Cell Value Is a Certain Value
Hi Everyone How can U set up an alert to format cells with a pink fill colour if say A1 ever has the value eg 1000 in it? This could be used to Highlight the Name and address of someone (with a pink fill) that has ever owed £1000 even if they now do not. In other words the name and address of that person stays in pink fill if ever they have owed £1000. Thanks for any help. Steve Lincoln UK turn cells get Excel record that a cell has ever been a certain value Conditional formatting. Look up 'Highlight cells that meet specific conditions' in the Answer wizard in...

Return range of cell values based on current date
I have a spreadsheet containing daily sales data for the month. I want today's sales data to display on a different worksheet to save the hassle of someone scanning through an entire month of data. Is this possible and what function do I use to achieve this? Thanks, Wing WHY NOT ADD A WORKBOOK? Providing a workbook will not only get you your answer quicker but will better illustrate your problem, usually when we can see your data (-it can be dummy data but must be of the same type-) and your structure it is far easier for us to give you a tailored, workable answer to ...

How do I keep my excel files in Excel 2003, not auto 2007
I loaded a trial version of excel 2007. I want to continue using 2003 instead of buying the full version, but my old spreadsheets keep automatically changing to 2007. Delete the trial version. Control Panel > Unistall Programs > Uninstall XL2007 Trial Version. Regards, Alan. "DorothyL" <DorothyL@discussions.microsoft.com> wrote in message news:C2722C89-5C3A-4F1D-814C-585E8D49DC5D@microsoft.com... >I loaded a trial version of excel 2007. I want to continue using 2003 > instead of buying the full version, but my old spreadsheets keep > automatic...

Server upgrade (hardware, O/S, but keep 5.5)
I'm planning an upgrade to our old tired Exchange server. We have 60 users, using Outlook 2000 and Outlook 2003. Currently we have Exchange 5.5 on an old PII NT 4 server. And OWA on a seperate IIS server. I'm planning to upgrade to a single CPU Dell server, with SCSI RAID. At the same time I thought I'd implement Active Directory, as we have an existing Windows 2000 file/print server as a member server. I don't really want to upgrade from Exchange 5.5 at this time (cost, and we're not sure we will stay with Exchange long term) so I assume I can't run Exchange 5.5 on ...

Absolute Worksheet reference number
When one references a cell on a different sheet one uses, for instance, Sheet1!A1 to get at the value of that cell. If you change the name of the worksheet from Sheet1 then the reference is lost. Is there no way of still refering to the 'logical' sheet name (e.g. Sheet1) after the sheet has been renamed? My problem is that I am trying to generate product statistics on a "compoite" worksheet and the other worksheets contain certain parameters on each batch . The worksheet tab name is also changed to the batch number and the numbers are not contiguous (i.e. do not follow ...

Macro to coppy cells to certain rows depending on value in cell
I want his macro to after it have inserted the colmns and added the formula to 1. copy range A1 to E1 to every row where the word "Header" is in colmn F. 2. Then copy paste the whole sheet as values. 3. Then the range now standing left of "header" must be copied to the empy cells beneath each heading. For example a b c d e f 1)12/12/2005 F001 SAO3 1 CCE Header 2) Detail ...

define a cell with the value of anothe cell
I'm very new to excel and i think i just don't understand a basic function but i couldn't find it under the help menu. how can you define a cell using a letter and then a value of another cell? here is an example W15=5 D5=10 my guess was =D(W15) which i would like to equal 10, but i get a name error Hi Todd Try =INDIRECT("D"&W15) -- Regards Roger Govier Todd Duncombe <Todd Duncombe@discussions.microsoft.com> wrote: > I'm very new to excel and i think i just don't understand a basic > function but i couldn't find it under the help me...