copying groups with formulas
in [General]
|
From: Kyle P. on 29 Apr 2010 09:15 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.
From: Tom Hutchins on 29 Apr 2010 09:59 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 "Kyle P." wrote: > 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.
From: Kyle P. on 29 Apr 2010 10:55 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. "Tom Hutchins" wrote: > 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 > > "Kyle P." wrote: > > > 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.
From: Kyle P. on 29 Apr 2010 11:06 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. "Tom Hutchins" wrote: > 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 > > "Kyle P." wrote: > > > 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.
From: Tom Hutchins on 29 Apr 2010 11:46
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 "Kyle P." wrote: > 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. > > "Tom Hutchins" wrote: > > > 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 > > > > "Kyle P." wrote: > > > > > 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. |