Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas
How do I copy a formula from a group of cells to another group of cells
without excel editing the formula? For example, I want the exact same formula from one group copied to another group - as is - without any changes to it at all. For example, I am using the formula ='10U'!A2 in one cell and want to go down 15 cells and have that exact formula pasted there. When I do that, excel automatically changes the formula to ='10U'!A16. I know I can copy from the formula bar, but what if I want to do this for a large number of cells? It would take me forever. Help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas
cyn22, maybe using the formula ='10u'!A$2 will work for you, does this help?
-- 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 appreciated! Using Excel 2002 & 2003 "cyn22" <u16589@uwe wrote in message news:58b2343684e40@uwe... How do I copy a formula from a group of cells to another group of cells without excel editing the formula? For example, I want the exact same formula from one group copied to another group - as is - without any changes to it at all. For example, I am using the formula ='10U'!A2 in one cell and want to go down 15 cells and have that exact formula pasted there. When I do that, excel automatically changes the formula to ='10U'!A16. I know I can copy from the formula bar, but what if I want to do this for a large number of cells? It would take me forever. Help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas
When creating cell references you can lock in either the ColRef or the RowRef
or both by the placement of dollar signs. In your example, by changing ='10U'!A2..... to: ='10U'!$A2 when you copy/paste you'll lock in Col_A or to: ='10U'!A$2 when you copy/paste you'll lock in Row_2 or to: ='10U'!$A$2 when you copy/paste you'll lock in Col_A and Row_2 Note: When editing a cell reference...each time you press the [F4] key, Excel will add or remove dollar signs. Play with that. Does that help? *********** Regards, Ron "cyn22" wrote: How do I copy a formula from a group of cells to another group of cells without excel editing the formula? For example, I want the exact same formula from one group copied to another group - as is - without any changes to it at all. For example, I am using the formula ='10U'!A2 in one cell and want to go down 15 cells and have that exact formula pasted there. When I do that, excel automatically changes the formula to ='10U'!A16. I know I can copy from the formula bar, but what if I want to do this for a large number of cells? It would take me forever. Help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas
Use absolute reference
='10U'!$A$2 -- Regards, Peo Sjoblom (No private emails please) "cyn22" <u16589@uwe wrote in message news:58b2343684e40@uwe... How do I copy a formula from a group of cells to another group of cells without excel editing the formula? For example, I want the exact same formula from one group copied to another group - as is - without any changes to it at all. For example, I am using the formula ='10U'!A2 in one cell and want to go down 15 cells and have that exact formula pasted there. When I do that, excel automatically changes the formula to ='10U'!A16. I know I can copy from the formula bar, but what if I want to do this for a large number of cells? It would take me forever. Help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas
Paul B,
That works great...however, how do I now go back and put that dollar sign after the row in a large number of cells? I have a bunch of cells I need to change. I know you can replace a number or letter with the edit/replace function, but can I blanket add something? Paul B wrote: cyn22, maybe using the formula ='10u'!A$2 will work for you, does this help? How do I copy a formula from a group of cells to another group of cells without excel editing the formula? [quoted text clipped - 5 lines] formula to ='10U'!A16. I know I can copy from the formula bar, but what if I want to do this for a large number of cells? It would take me forever. Help! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas
You can use code or another workaround
do editreplace and in the find box put an equal sign = then in the replace with box put ^^, select replace all, close dialogue box, now copy and paste, then reverse and replace ^^ with equal sign done! -- Regards, Peo Sjoblom (No private emails please) "cyn22" <u16589@uwe wrote in message news:58b264dacabdc@uwe... Paul B, That works great...however, how do I now go back and put that dollar sign after the row in a large number of cells? I have a bunch of cells I need to change. I know you can replace a number or letter with the edit/replace function, but can I blanket add something? Paul B wrote: cyn22, maybe using the formula ='10u'!A$2 will work for you, does this help? How do I copy a formula from a group of cells to another group of cells without excel editing the formula? [quoted text clipped - 5 lines] formula to ='10U'!A16. I know I can copy from the formula bar, but what if I want to do this for a large number of cells? It would take me forever. Help! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas
Here's code how to change the references
Sub ReltoAbs() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlAbsolute) Next End Sub press alt + F11, do insertmodule and paste the above, press alt +Q to close the VBE, select the formulas and press alt + F8 and doublclick the macro to reverse Sub AbstoRel() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlRelative) Next End Sub -- Regards, Peo Sjoblom (No private emails please) "Peo Sjoblom" wrote in message ... You can use code or another workaround do editreplace and in the find box put an equal sign = then in the replace with box put ^^, select replace all, close dialogue box, now copy and paste, then reverse and replace ^^ with equal sign done! -- Regards, Peo Sjoblom (No private emails please) "cyn22" <u16589@uwe wrote in message news:58b264dacabdc@uwe... Paul B, That works great...however, how do I now go back and put that dollar sign after the row in a large number of cells? I have a bunch of cells I need to change. I know you can replace a number or letter with the edit/replace function, but can I blanket add something? Paul B wrote: cyn22, maybe using the formula ='10u'!A$2 will work for you, does this help? How do I copy a formula from a group of cells to another group of cells without excel editing the formula? [quoted text clipped - 5 lines] formula to ='10U'!A16. I know I can copy from the formula bar, but what if I want to do this for a large number of cells? It would take me forever. Help! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Formulas
Hi cyn22,
And another way is: Cut the top cell of the original bunch then paste into top cell of new bunch. Fill this pasted top cell of new bunch down to desired bottom row. Go to the cell in the origin bunch that is below the cell you cut and copy/paste or fill up to return it to its original state. Pasting from a Cut does not alter formula's references, but it does delete it from the cell you cut and therefore has to be replaced by Copy/Paste or filling from a neighbouring cell with the same formula Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying formulas in vba | Excel Discussion (Misc queries) | |||
Excel Error when copying formulas | Excel Worksheet Functions | |||
excel 2002 - copying formulas to another worksheet | Excel Discussion (Misc queries) | |||
Excel 2002 - copying formulas across worksheets | Excel Discussion (Misc queries) | |||
Copying options: contents, results, formulas, etc. | New Users to Excel |