Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying groups with formulas
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying groups with formulas
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying groups with formulas
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying groups with formulas
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying groups with formulas
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying groups with formulas
Select range to copy and then EditReplace = with ^^ cut/copy you range and
reverse the Edit Replace after pasting. -- Regards Dave Hawley www.ozgrid.com "Kyle P." wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Sorting groups in groups due to subtotaling | Excel Worksheet Functions | |||
Copying formulas | Excel Worksheet Functions | |||
Formulas using the same data types but with varying size groups | Excel Discussion (Misc queries) | |||
Copying groups of dates | Excel Discussion (Misc queries) | |||
how do i view all groups under excel in google groups | Excel Discussion (Misc queries) |