Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a formula "literally"
Say I have the following relative reference formulas in Col A:
+D1 +D2 +D3 I want exactly the same formulas in Col B: +D1 +D2 +D3 I can change the formulas in Col A to absolute reference, copy/paste them, and then change all formulas back to relative. Along similar lines I could add an apostrophe in front of each formula before copying it, thereby changing it to text, then removing it after the copy/paste. But these methods are manual and cell by cell. Is there a quicker way to do this, short of writing VBA code? Art |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a formula "literally"
"ArthurJ" wrote:
.. I have the following relative reference formulas in Col A: +D1 +D2 +D3 I want exactly the same formulas in Col B: +D1 +D2 +D3 .... Not a direct answer probably, but on the face of it, why not just point col B to col A ? eg: in B1: = A1, with B1 copied down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a formula "literally"
In column B, enter the first formula
=D1 Then, select the cell w/this formula, put your mouse on the little black cross in the bottom right corner, click and hold the left mouse button while you drag the formula down as far as you need. "ArthurJ" wrote: Say I have the following relative reference formulas in Col A: +D1 +D2 +D3 I want exactly the same formulas in Col B: +D1 +D2 +D3 I can change the formulas in Col A to absolute reference, copy/paste them, and then change all formulas back to relative. Along similar lines I could add an apostrophe in front of each formula before copying it, thereby changing it to text, then removing it after the copy/paste. But these methods are manual and cell by cell. Is there a quicker way to do this, short of writing VBA code? Art |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a formula "literally"
Perhaps more directly to your subject line ..
In the source sheet, Select the range of formula cells Click Edit Replace Find: = Replace with: zzzzz (say) Click Replace All Then select the range copy and in the destination sheet, Right-click Paste Click Edit Replace Find: zzzzz Replace with: = Click Replace All -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a formula "literally"
Copying a formula "literally"
Hi. Any copy operation puts Excel into CutCopyMode, and will adjust the references. As one alternative, see if this macro will work for you. Sub Demo() Columns(2).Formula = Columns(1).Formula End Sub -- HTH. :) Dana DeLouis Windows XP, Office 2003 "ArthurJ" wrote in message ... Say I have the following relative reference formulas in Col A: +D1 +D2 +D3 I want exactly the same formulas in Col B: +D1 +D2 +D3 I can change the formulas in Col A to absolute reference, copy/paste them, and then change all formulas back to relative. Along similar lines I could add an apostrophe in front of each formula before copying it, thereby changing it to text, then removing it after the copy/paste. But these methods are manual and cell by cell. Is there a quicker way to do this, short of writing VBA code? Art |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a formula "literally"
Max,
If you place a $ in front of the column reference (i.e. +$D1), it will always keep your cell reference on column D. The same thing applies to rows if you place a $ in front of the row reference (i.e. +D$1). You can also have a static cell reference by placing a $ in front of both the column and row reference (i.e. +$D$1). In addition, you can copy the cells with the formula you need and Paste Special -- Formulas where you want the new formulas. I hope that helps! Jan |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a formula "literally"
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a formula "literally"
If you want the exact copy vs relative, simply place an apostrophe (') ahead of the equals sign in the first formula, similar to adding comments in some programming languages; this changes it from a formula to text. Then copy it to the new cell, then remove the apostrophes from both. Incidentally, this works pretty well if you are trying to debug a long formula and Excel won't let you enter the formula with a mistake in it. You can "comment out" the entire formula, then copy it and work on pices of the formula until you find the fix. -- gjcase ------------------------------------------------------------------------ gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061 View this thread: http://www.excelforum.com/showthread...hreadid=509176 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a formula "literally"
I wouldn't use an apostrophe. Those are very difficult to clean up with
Edit|replace's. I'd use: edit|replace what: = (equal sign) with: $$$$$= replace all. That unique string $$$$$= should be easy to change back to a single equal sign. gjcase wrote: If you want the exact copy vs relative, simply place an apostrophe (') ahead of the equals sign in the first formula, similar to adding comments in some programming languages; this changes it from a formula to text. Then copy it to the new cell, then remove the apostrophes from both. Incidentally, this works pretty well if you are trying to debug a long formula and Excel won't let you enter the formula with a mistake in it. You can "comment out" the entire formula, then copy it and work on pices of the formula until you find the fix. -- gjcase ------------------------------------------------------------------------ gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061 View this thread: http://www.excelforum.com/showthread...hreadid=509176 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying a formula to multiple worksheets | Excel Worksheet Functions | |||
Returned: Copying a formula horizontally, the source data is verti | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
How do I prevent a formula element from incrementing when copying | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |