×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to copy a formula without changing references
2

How to copy a formula without changing references

How to copy a formula without changing references

(OP)
Hav you ever wanted to copy a formula from one cell to another without changing the referenced cells?

Heres a small pices of code that can do that:

Dim myvalue As String

Sub move_formula1()
    myvalue = ActiveCell.Formula
End Sub
 
Sub move_formula2()
    ActiveCell.Value = myvalue
End Sub

Link to two subs to keys on the keyboard (eg: q and w) and you will have this little function at your fingertips!

-Be aware crtl+q/w might be linked to functions that you frequently (i dont).

Best Regards

Morten

RE: How to copy a formula without changing references

There's a simpler approach: select the formula excluding the initial '=' and Copy it, then go to the destination cell, type '=' and then Paste it.

prex
motori@xcalcsREMOVE.com
http://www.xcalcs.com
Online tools for structural design

RE: How to copy a formula without changing references

(OP)
well thats exactly what i dont find "simple"

Best Regards

Morten

RE: How to copy a formula without changing references

Actually, you can copy the equals sign with the cell references in the original equation, then click on the target cell and paste it directly (no need to press "=" then paste it... takes less than 5 seconds).

jproj

RE: How to copy a formula without changing references

(OP)
yes of course but then the references in the formula will change! Thats what i tried to solve. Maybe its just me - but on occation i have to "move"/"copy" a formula without changin the references when moved.

Best Regards

Morten

RE: How to copy a formula without changing references

Thanks to jproj!
I never noticed that you can Copy and Paste including the equal sign: sometimes you take an habit and continue using it over the years, without noticing the shorter path.
To Morten: of course the problem you raised is quite common and needs a simple solution, but the solution of jproj is the simplest way for me. By the way you don't have to copy the cell content (in this case the references are modified), but you must select (highlight) the formula in the formula bar, then copy and finally paste in the destination.

prex
motori@xcalcsREMOVE.com
http://www.xcalcs.com
Online tools for structural design

RE: How to copy a formula without changing references

(OP)
OK with me

RE: How to copy a formula without changing references

Too bad we're all pigeon-holed into using Excel.  In the old days, with Quattro Pro you could cut a cell and then paste it in multiple locations.  Any other cells referencing the cut cell will all be converted to look at wherever the first paste was.  So I would cut it, paste it in the same place to maintain any other references, and then paste it in your other location maintaining the references in that cell.  I consider that pretty efficient with the [ctrl]-[x] and [ctrl]-[v] keystrokes.

Another Quattro Pro advantage was that a copied cell stayed on the clipboard until something else was copied.  So you could paste without still having that stupid dotted line around the copied cell(s).  You could even copy a cell, delete it, and then hit paste somewhere else.  Try doing that in Excel with any efficiency.

I've heard you could get around this by that clipboard (of the last 12? copies) that pops up ocassionally, but like that stupid "help" Paperclip in the lower right corner (that I uninstall on everything I use), I find it not to be useful enough or worth my time to figure out how to use.

Sorry for the OT (off-topic) and ranting,
Ken

RE: How to copy a formula without changing references

Or you could edit the cell you want to copy and place the cursor where the cell ref is that you want to keep, then hit the F4 key and it will place a $ symbol with your cell ref. Works quite well if you need to keep some cell refs and not others in the one formula.


sc

RE: How to copy a formula without changing references

(OP)
Of course the $ works fine but if you havnt got it in there from then beginning then its tiresome to add $$$ (UNLESS YOU GET $$$ BY THE HOUR

Anyway event though i seem to be the only one who finds this usefull i improved it slightly (yes it IS possible

Dim myvalue As Variant

Sub move_formula1()
    myvalue = Selection.Formula
End Sub
 
Sub move_formula2()
    Selection.Value = myvalue
End Sub

Using selection instaed of activecell makes it possible to copy ranges. But you have to select a range also when "pasting"

Best Regards

Morten

RE: How to copy a formula without changing references

MortenA,

To add $, you can use "F4" key, it will save you time.
Before you add "$", place the sursor where you want to add.

Regards,

jackzhong

RE: How to copy a formula without changing references

Another option would be to name the cells you wish to refer to.  Since Excel defines Names with $ signs, copy and paste works.
Defining Names also really helps with debugging later...

Profit = Revenue-Cost, is a lot easier to understand than F4=E3-B2

RE: How to copy a formula without changing references

I know this may now be a bit late but you can alklso do it by:

copying the cell (not its contents) and using paste special and selecting formulas.

RE: How to copy a formula without changing references

I can't believe I never thought of copying the contents of text in the formula bar, until you guys thought of it for me. It's so simple, it's brilliant.
 
Thanks, guys.

RE: How to copy a formula without changing references

I think the best way to do this (for many cells) is copy from the cells and then paste special -> paste link.

RE: How to copy a formula without changing references

prex and jproj: OK for your tips!!  Sorry for the delay, but I just found this site yesterday!!

RE: How to copy a formula without changing references

To copy to a different cell in the same column, you can Copy, dragging with the +cursor, through any number of empty cells to the right or left, then Move any distance up or down, then Copy back to the original column, then delete the extraneous cells to which you have copied the formula.  The reference cells will be unchanged.  

To copy to a different cell in the same row, copy upward or downward through any number of empty cells, Move horizontally to the new column, Copy up or down to the original row, etc.  I know this is only of use in certain cases, but hope it helps.

RE: How to copy a formula without changing references

Another alternative I have used in the past is to
1. copy the original sheet
2. drag the formula cell to the new position, on the copy, that you require.
3. copy the formula back to the original sheet
4. delete the copy.

RE: How to copy a formula without changing references

hmm... I thought this had been mentioned before, but in re-reading I didn't find it.

hit ctrl-`  (ctrl ~)  to switch to "equations" view
select and copy the equations
paste them into notepad
you can switch back into "results" view at this point, if you like, by hitting ctrl-` again
ctrl-a to select all in notepad, then ctrl-c to copy
paste into excel, wherever you like.

This has the same effect as copying the text from the formula bar, but it works on multiple cells at the same time.

RE: How to copy a formula without changing references

Hmmm.

Interesting shortcuts. I could only make one cell in a selected range change to the formula view with [ctrl]+'

This can be accessed more easily by pressing F2 (Same keystroke allows you to rename files in Windows Explorer and various other programs)

What I did find was that [ctrl]+# changes a selected set of cells to date format and [ctrl]+@ changes them to time format. [ctrl]+~ doesn't seem to do anything on my version of Excel.

Also, as a totally unexpected shortcut key, [alt gr]+F2 opens the open file window (Same as [ctrl]+o ) - I found this accidentally while working in Word as you can change the format of a heading to Heading level by holding down [alt gr] and pressing the number of the heading level required.

Is there a list of shortcuts available for Excel? Very few of them are written by their menu items, so a lot of the time I use the underlined letters in menu items to select them instead [alt]+oe to format cells is probably the shortcut I use most.

Another interesting one is that [ctrl]+r will copy the cell to the left of the selected cell into the selected cell. I only found this one because [ctrl]+l, r and e in Word allow you to left, right and centre align text.

RE: How to copy a formula without changing references

I was just trying to differentiate between the
' and ` keys when I said ctrl ~ .  ~ is shift + `, of course.  The eqn-view shortcut is accessed via ctrl ` in excel 97 and 2000; I'm not sure about other versions.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close