Copying formulas to other cells. Keeping references w/o $ sign.

How do I copy formulas from a large group of cells to another group of
cells and have them all keep the references to the same cells? (It
normally changes the referneces to cells that have the same position in
relation to the former cell) I don't want to go in and change all of the
references, adding the $ sign.


--
GregP1962
------------------------------------------------------------------------
GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063
View this thread: http://www.excelforum.com/showthread...hreadid=535102

I usually try to put the $ before dragging the cells, but when I want a copy
of the data somewhere that refers back, I to the first cell where I want the
data, enter "=" and click the first cell of the area where I want to copy
from. Then drag to expand to the whole area.

"GregP1962" wrote:


How do I copy formulas from a large group of cells to another group of
cells and have them all keep the references to the same cells? (It
normally changes the referneces to cells that have the same position in
relation to the former cell) I don't want to go in and change all of the
references, adding the $ sign.


--
GregP1962
------------------------------------------------------------------------
GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063
View this thread: http://www.excelforum.com/showthread...hreadid=535102

You can do:
Copy
PasteSpecial
PasteLink
It will not carry the formulas in new cells but since it links your new
cells to the copied cells, it will give the same results as if the
formulas are there.


--
renegan
------------------------------------------------------------------------
renegan's Profile: http://www.excelforum.com/member.php...o&userid=10450
View this thread: http://www.excelforum.com/showthread...hreadid=535102

OK, The paste special, (using the "values" option) just moved the result
of the formula without moving the formula. So, any future changes will
not show in the copied cell.


If I use the = sign in the cell I am copying to, it just makes a
reference to that cell.

What I want is to copy the same formulas with data from the SAME cells
as the formulas I am copying from without having to go into every
formula to add th $ sign.


--
GregP1962
------------------------------------------------------------------------
GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063
View this thread: http://www.excelforum.com/showthread...hreadid=535102

Didn't Gary's Student's suggestion work for you?

First select the cells
Then use find/replace to change all = into x=
This will change all the formulae into text strings.

Select the range to copy
Edit|replace
what: = (equal sign)
with: $$$$$ (I like this better than x=
replace all

copy and paste these text strings to where you want them.

Then select that pasted range
edit|replace
what: $$$$$
with: =
replace all

Now the text strings are converted back to formulas.

Don't forget to fix the original range, too.



GregP1962 wrote:

OK, The paste special, (using the "values" option) just moved the result
of the formula without moving the formula. So, any future changes will
not show in the copied cell.

If I use the = sign in the cell I am copying to, it just makes a
reference to that cell.

What I want is to copy the same formulas with data from the SAME cells
as the formulas I am copying from without having to go into every
formula to add th $ sign.

--
GregP1962
------------------------------------------------------------------------
GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063
View this thread: http://www.excelforum.com/showthread...hreadid=535102

--

Dave Peterson

But, how do you "Then use find/replace to change all = into x="?

Selecting edit/find/replace give a very confusing dialog box. If I had
all afternoon, I'd try to figure that box out by trial and error.


--
GregP1962
------------------------------------------------------------------------
GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063
View this thread: http://www.excelforum.com/showthread...hreadid=535102

Select the range that should be adjusted.

Hit Ctrl-h (or edit|Replace)

In the "find what" box, type an equal sign.

In the "replace with" box, type $$$$$

click replace all.

Remember how you got it to work. You'll have to do it 2 more times to reverse
what you did.



GregP1962 wrote:

But, how do you "Then use find/replace to change all = into x="?

Selecting edit/find/replace give a very confusing dialog box. If I had
all afternoon, I'd try to figure that box out by trial and error.

--
GregP1962
------------------------------------------------------------------------
GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063
View this thread: http://www.excelforum.com/showthread...hreadid=535102

--

Dave Peterson

OK, that didn't work. I think the reason is that the forumlas have some
$ in them. When I try to change things back to the =, the ones that are
supposed to be $ get changed to =.


--
GregP1962
------------------------------------------------------------------------
GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063
View this thread: http://www.excelforum.com/showthread...hreadid=535102

Can you close your workbook without saving?

If yes, then do that and reopen the workbook.

Try it again and be very careful what you type into each of those boxes in the
Edit|Replace dialog.

If it doesn't work, then post back exactly what you typed into each of the
boxes.

This technique will work when you do it correctly.



GregP1962 wrote:

OK, that didn't work. I think the reason is that the forumlas have some
$ in them. When I try to change things back to the =, the ones that are
supposed to be $ get changed to =.

--
GregP1962
------------------------------------------------------------------------
GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063
View this thread: http://www.excelforum.com/showthread...hreadid=535102

--

Dave Peterson

OK, I got it. I used the find/replace to change the column names in the
new location. Thanks, I had never used the find/replace function.


--
GregP1962
------------------------------------------------------------------------
GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063
View this thread: http://www.excelforum.com/showthread...hreadid=535102