Copy/paste cells but formulas are updated incorrectly

[SKYTALKER]

When I copy/paste cells, the formulas in the cells are updated, but they're usually off by 1 or more cells. Sorry if I am not explaining this very well. I'll try to explain using the attachment.

I copy the block of cells beginning at: X180 - AD180 and then down to X217 - AD217. I then try to make a copy of the copied block of cells to X232 - AD232. This works out OK, except when I look at the formulas, they are usually 1 or more rows off, i.e. the formula in AA232 reads "=AA178-AA229" and it should read "AA180-AA229", which puts the whole batch of cells 2 rolls off. I've tried various methods of copying/moving the data, with the same results. What I end up doing is going through the whole batch and manually correcting the formulas. What am I doing wrong and what can I do to correct it? OR is there an easier method that I can use to correct the formulas, instead of manually changing each cell's formula?

I hope I explained this correctly, if not I apologize for my ignorance. Thanks for any help.

[djauncey]

Right I think I've worked out the issue. Basically it's because the number of rows that AA180 is looking up ie AA126 (54 rows above) and AA177 (3 rows above) is different to the number of rows separating AA232 and where it should be looking up AA180 is only 52 rows.

The only way to counter this really is to make sure that each 'batch' of formula is the same size.

Hope this helps

[SKYTALKER]

Hey djauncey,
First of all, thank you so much for your response. I sincerely appreciate it.
Although I've used computers since the 70's, everything I know is self-taught. As for your explanation, I'm not positive I understand what you're conveying. Whenever I copy the batch of cells that I want to copy into the worksheet, I copy them as a group, i.e. I select cells X232 - AD269 and then copy them using CNTL-C. Then I highlight cells X288 - AD288 and insert the cells X232-AD269 into the table, which moves X288-AD288 down. I utilize the "Insert" and then "Insert Cell" options of the Home Ribbon. Does this make sense? You wrote:

[B]"Basically it's because the number of rows that AA180 is looking up ie AA126 (54 rows above) and AA177 (3 rows above) is different to the number of rows separating AA232 and where it should be looking up AA180 is only 52 rows. The only way to counter this really is to make sure that each 'batch' of formula is the same size."[/B]

I don't understand what you wrote here because when I highlight the cells to be copied, they're in one long block, so there's no way the rows should be different. Unless I misunderstand what you wrote, which is a possibility. If I'm incorrect, could you explain it in another way???
Thanks,
Cliff

[djauncey]

Hi Skytalker,

Sorry it's a bit of a difficult one to explain, when you copy formulas they move the cell reference so that it is looking up the cell in the same position, so the formula in AA180 looks up cell AA126; 54 cells up from the formula. When you then copy the formula into a new cell; AA232 it will copy the formula looking up the cell that is 54 cells up. The only way to counter this is to make sure that the spacing between the formula and the cell you want it to lookup is exactly the same throughout.

Hope this helps

[SKYTALKER]

I'm so sorry for being so dunce, but I still am unable to understand what you are explaining. I will look up "cell reference" and see if that will help me understand what you're trying to explain. I guess where my stumbling block is the 54 cells. I would assume that when I copy a block of cells that contain formulas which reference other cells, Excel would take that into account and it does, but it also mysteriously adds a row which throws the formulas off by that row. What is blowing my mind is there are times when I copy the block of cells and everything works out prefectly. Does my stumbling block have to do with "cell references"? Are there any particular help pages that would aid me in understanding this problem better? I don't want you to take a lot of time trying to get me to understand what is probably an elementary subject that a 5th grader would understand. I'm more than willing to research whatever subject you think would help me...just name it. Oh, and as always, I appreciate your time and knowledge...Thank you, thank you, and thank you!
Cliff

[djauncey]

Hi Cliff,

Cell reference just means the cell that the formula is looking up ie A3, B7 etc.

The issue is that Excel has 2 options for when you move cells, you can use the $ signs; such as $A1 to lock the column and A$1 to lock the row and $A$1 to lock that exact cell, or you have a free formula, which means that when you move the formula from 1 cell to another it will lookup the Cell the same number of columns and rows away as it was before.

The same way that if you have in cell D1 =a1+b1 and then copy the cell and move it to F2 it will 'correct' the formula to =c2+d2, because in the original formula it was in fact doing =(the cell on the same row and 3 cells left) + (the cell on the same row and 2 cells left)

Similarly when you move the formula from AA180 its looking up the cell in the same column 54 cells above it, so when you copy the cell to AA232 it will look up the cell in the same column 54 cells above it.

Hope this clears it up somewhat?

Hope this helps