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

G

GregP1962

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.
 
G

Guest

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.
 
R

renegan

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

GregP1962

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.
 
D

Dave Peterson

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.
 
G

GregP1962

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.
 
D

Dave Peterson

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.
 
G

GregP1962

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 =.
 
D

Dave Peterson

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.
 
G

GregP1962

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.
 
D

Dave Peterson

Now that you've used the Edit|replace dialog, you may want to try the other
suggestion.

Save your workbook first. If you screw it up, you can close without saving and
do no harm.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top