Copying cells changes value?

[Xenoun]

This isnt a big issue, but more of an annoyance. I have cells which equal cells on another sheet in the workbook, so that I can mainpulate and present them in a better manner.

When I try to copy the cells which refer to the other sheet the value changes. For example, on one page of sheet 2 i have cells which are set to equal cells in sheet 1. When I copy the cells in sheet 2 to another page in sheet 2, the cell references change. It also seems to give a different result if I copy the cells to a page below it or next to it. Copying to a page below increases the cell reference number (i.e. =B3 becomes =B40) and copying horizontal changes the letter reference (=A2 becomes =AP2)

It's not a huge deal, but i'm wondering why this occurs and if there is a way to stop it from happening. (Cut and paste works fine).

 

[syntaxed]

You can add $ signs before the Row and Column references.

e.g. instead of =B3 you can have =$B$3

An easy way to do this is to click on the cell reference in the formula bar and press F4

 

[Dryver14]

if you want to remove the links once you have pasted do the following:

when you select the paste select paste special
click the values button
click ok

 

[tusharm]

It's intentional and it's known as a 'relative reference.' What that means is that when you copy the formula to another cell the 'relative' difference remains the same. To understand this, try the following.
In A1 enter 1. In B1 enter =A1+1. Now, copy B1 to C1. You will see C1 contains =B1+1.

Now, change the formula in B1 to =$A$1+1. Copy this to C1. What do you see?

The $ sign changes the reference to the next token to absolute. Consequently, one can create a combination of relative and absolute references as in $A1 and A$1.

 

[Xenoun]

I think i've slightly mis stated the problem. I nkow about excel's relative reference system and using F4 to fix values etc, but this is slightly different

Here's an Example:

On sheet 1 I have data listed in columns.

Say it goes from A1 to A40

Then on sheet 2, to arrange the data better, I have something like

B2 = 'sheet1'!A1

Then on sheet 2, I need to copy that B2 cell and want to keep the same format etc, and I copy it down a page to B50

By doing this I dont get B50 = 'sheet2'!B2

It gives me B50 = 'sheet1'!A51 or something similar

So in other words when I copy, excel gives me a completely different value from the cell i copied from and the cell that the original refers to.

Im guessing that F4 will fix this, but it just seems very strange that it happens at all