Copying Formulas

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

 

[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

 

[Guest]

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

 

[Peo Sjoblom]

Use absolute reference

='10U'!$A$2

--
Regards,

Peo Sjoblom

(No private emails please)

 

[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?

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!

 

[Peo Sjoblom]

You can use code or another workaround
do edit>replace 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)

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?

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!

 

[Peo Sjoblom]

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 insert>module 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)

You can use code or another workaround
do edit>replace 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)

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?

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!

 

[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