Copying Formulas

C

cyn22

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!
 
P

Paul B

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
 
R

Ron Coderre

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
 
P

Peo Sjoblom

Use absolute reference

='10U'!$A$2

--
Regards,

Peo Sjoblom

(No private emails please)
 
C

cyn22

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 said:
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?
Click to expand...
[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!
Click to expand...
Click to expand...
 
K

Ken Johnson

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
 
Top