freezing formulas when cells move
 

I have an inventory spreadsheet where I compare the values in 2 columns to
make sure they equal 0. If the 2 values in each row to equal 0 when I
subtract one from the other (A23-B23), this lets me know that the inventory
price has not changed. However, because the new inventory list may have more
or less items that the current inventory list, I have to move cells up or
down to align the similar inventory parts.

I want to put a column to the side where I subtract current inventory value
from new inventory value and get a result of 0. But when I move one column up
or down, the formula changes accordingly. I want to be able to freeze the
formula column so that it always says "A23-B23" even when I move A23 up or
down.

freezing formulas when cells move
 

Try something like this:

C1: =OFFSET(C1,0,-1)-OFFSET(C1,0,-2)
Copy that cell down as far as needed

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"BradleyP" wrote:

I have an inventory spreadsheet where I compare the values in 2 columns to
make sure they equal 0. If the 2 values in each row to equal 0 when I
subtract one from the other (A23-B23), this lets me know that the inventory
price has not changed. However, because the new inventory list may have more
or less items that the current inventory list, I have to move cells up or
down to align the similar inventory parts.

I want to put a column to the side where I subtract current inventory value
from new inventory value and get a result of 0. But when I move one column up
or down, the formula changes accordingly. I want to be able to freeze the
formula column so that it always says "A23-B23" even when I move A23 up or
down.

freezing formulas when cells move
 

What to try again? I am confused. What is meant by "moving A23 up or down"?
Do you mean insert or delete rows?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"BradleyP" wrote in message
...
I have an inventory spreadsheet where I compare the values in 2 columns to
make sure they equal 0. If the 2 values in each row to equal 0 when I
subtract one from the other (A23-B23), this lets me know that the
inventory
price has not changed. However, because the new inventory list may have
more
or less items that the current inventory list, I have to move cells up or
down to align the similar inventory parts.

I want to put a column to the side where I subtract current inventory
value
from new inventory value and get a result of 0. But when I move one column
up
or down, the formula changes accordingly. I want to be able to freeze the
formula column so that it always says "A23-B23" even when I move A23 up or
down.

freezing formulas when cells move
 

Yes, I am inserting or deleting cells in one column or the other to align the
inventory because the 2 lists aren't completely identical. I then subtract
the 2 prices and if I get 0, there is no price change. Basically, I am
comparing 2 lists but I have to align them first. I can't think of another
way to do this.

Thanks,

"Bernard Liengme" wrote:

What to try again? I am confused. What is meant by "moving A23 up or down"?
Do you mean insert or delete rows?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"BradleyP" wrote in message
...
I have an inventory spreadsheet where I compare the values in 2 columns to
make sure they equal 0. If the 2 values in each row to equal 0 when I
subtract one from the other (A23-B23), this lets me know that the
inventory
price has not changed. However, because the new inventory list may have
more
or less items that the current inventory list, I have to move cells up or
down to align the similar inventory parts.

I want to put a column to the side where I subtract current inventory
value
from new inventory value and get a result of 0. But when I move one column
up
or down, the formula changes accordingly. I want to be able to freeze the
formula column so that it always says "A23-B23" even when I move A23 up or
down.

freezing formulas when cells move
 

The formula works but this is the problem:

In this example, old inventory and new inventory line up across. No cell
movement is needed and the formula works.
A B c d
e
1 old item x 5 New item x 5
=B1-D1(result is 0)
2 old item y 5 New item y 6
=B2-D2(result is -1)

However,

In this example, I have to move cells A1 and B1 down one cell to align the
inventory. If I do this, the formula changes from =B1-D1 to =B2-D1. I want
the formula to still say =B1-D1 even when I move the cells referenced in the
formula up or down. The formula you gave does the same thing when I move the
referenced cells up or down.
A B c d
e
1 old item x 5 New item a 5
=B1-D1(result is 0)
2 old item y 5 New item x 6
=B2-D2(result is -1)
3 old item z 5 New item y 6
=B2-D2(result is -1)


"Ron Coderre" wrote:

Try something like this:

C1: =OFFSET(C1,0,-1)-OFFSET(C1,0,-2)
Copy that cell down as far as needed

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"BradleyP" wrote:

I have an inventory spreadsheet where I compare the values in 2 columns to
make sure they equal 0. If the 2 values in each row to equal 0 when I
subtract one from the other (A23-B23), this lets me know that the inventory
price has not changed. However, because the new inventory list may have more
or less items that the current inventory list, I have to move cells up or
down to align the similar inventory parts.

I want to put a column to the side where I subtract current inventory value
from new inventory value and get a result of 0. But when I move one column up
or down, the formula changes accordingly. I want to be able to freeze the
formula column so that it always says "A23-B23" even when I move A23 up or
down.