copying groups with formulas

  • Thread starter Thread starter Kyle P.
  • Start date Start date
K

Kyle P.

Hey,
When I have a group of cells, all with formulas that inter-relate to
other cells, and I try to copy them to another section of a spreadsheet, the
formulas all reset themselves to new cells, relative to their new location.

How do I move cells and keep their formulas relating to the cells they were
related to before the move?

Thanks as always everyone,

Kyle P.
 
If you MOVE cells (drag them to a new position, or CUT & paste them), they
will still refer to the same cells as they did before the move. If you COPY &
paste cells, any relative cell references in them will will change. To
prevent this, change the relative cell references in the cells to be copied
to absolute references. The symbol that tells Excel a refernce is absolute is
the dollar sign ($). You can make rows, columns, or both absolute:

A1 = relative reference to A1
$A1 = absolute column A, relative row
A$1 = relative column, absolute row 1
$A$1 = absolute reference to cell A1

In the formula bar, if you click on a cell address in a formula and press
F4, it will toggle through these absolute/relative options.

Hope this helps,

Hutch
 
That's great. I tried that already, but with only one $ before each cell
column. Didn't use one between the column letter and row number.

Is there a way to format that to a large group of related formulas, or do I
have manually chaneg each one before moving the group?

Also, I plan to move this to a second sheet. How do I link cells to the
first sheet?

Thanks for the Help,
Kyle P.
 
Never mind that first question, I see what you're saying about F4. Could
still use a way of linking cells across sheets though.
thanks,
kyle p.
 
One easy way, with both workbooks open:
- set up the formulas in the FROM workbook. save it.
- instead of copying the cells with the formulas, CUT them and paste in the
TO workbook. The formulas will refer back to the FROM workbook.
- close the FROM workbook without saving.
- save the TO workbook.

Hope this helps,

Hutch
 
Select range to copy and then Edit>Replace = with ^^ cut/copy you range and
reverse the Edit Replace after pasting.
 
Back
Top