#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
cyn22
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Paul B
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
cyn22
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Johnson
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
copying formulas in vba Michael Excel Discussion (Misc queries) 1 November 17th 05 09:48 PM
Excel Error when copying formulas Joe Gieder Excel Worksheet Functions 2 March 18th 05 06:01 PM
excel 2002 - copying formulas to another worksheet Greg Excel Discussion (Misc queries) 2 January 28th 05 11:23 PM
Excel 2002 - copying formulas across worksheets Greg Excel Discussion (Misc queries) 1 January 27th 05 11:45 PM
Copying options: contents, results, formulas, etc. Top Spin New Users to Excel 2 December 20th 04 05:54 PM


All times are GMT +1. The time now is 07:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"