Relative formula is not looking at the right row

[Shannon561]

Sorry for the bad title!

When i copy and past a line instead of it staying relative to the new position it is looking at a completly different row.

So the formula only looks at the cells in the row that it is in, but for some reason when it is being copied and pasted it changes the row it looks at.

None of the formulas are using absolute cell refernces so it should change the formula to be relative to that row but it doesn't

The formula's are simple as well one is just O227*N227, thats as about advanced as these formulas get!

And if i re enter the formula corectly and copy that down it then works again?

Does anyone have any ideas? Has anyone seen this before?

Thank you

[mcmahobt]

How are you copying and pasting it? Is the formula you gave an actual example of a formula that is producing the error? Can you attach a sample workbook to see if the error can be replicated?

[Shannon561]

That is actually on of the formulas. There are 700 oidd rows with multiple formulas in them and they work in the position they are in. Then the whole row is cut and moved to a different worksheet in the same file, that is when the formula starts looking at a row in the worksheet that it was pasted to. Let say it was cut from row 350 - Formula is O350*N350 - in the original worksheet and then pasted in the new worsheet on row 220 so the formula should be - O220*N220 -, but what happens is the formula becomes - O84*N84 - and if i go to row 84 it is working out that correctly?

Unfortuanetly because of the nature of the spreadsheet I am unable to post it. So if there are any ideas i appreciate the help but if the only way to fix it is to see it then i'll just have to leave it for now.

Thank you for the help.

[Jonmo1]

I'm unable to duplicate the behavior.
It behaves exactly as you'd expect it to.

There must be something else going on.
Are you doing the cut/paste by hand? or with a macro?
Is there any vba code in the book?

Does it do this on any sheet, any book, any formula?
Or just this one in particular?
Does it do it in a brand new book/sheet ?
Are there any merged cells in the range?
Are you only cutting that 1 row, or several rows at the same time?

Just for troubleshooting sake, try COPY and paste, instead of CUT

[Shannon561]

If you copy and paste it does do the same thing. This is intermittent as well!

This is a shared workbook so someone is probably screwing something up. I have never seen Excel be the one that isn't doing what it is told.

There are no Macros and there are no VBA code in this worksheet.

It seems to be isolated to this workbook. Other things have been happening to this workbook as well, like random lines scattered through every worksheet. I am convinced someone is at fault but i am not sure.

Here is the code that is only ever affected,

=0.87*(L2/1000) 200 1 =O2*N2 =(O2*L2)/1000 =U2/O2 5.54 =T2*R2

These are oviously in different cells