"IF" function question
 

Hi,
I have an IF function comparing 2 cells and it is under column C:

A B C
cat Dog "=if(A1=B1,1,0)" (here result is 0)
Dog bird "=if(A2=B2,1,0)" (here result is 0)

Now, every time the result is ZERO in column C, I will move the
corresponding data on column B downwards "Insert - Shift cells down". The
problem here is that my formula on column C also moves downwards! So if I
moved down the data on B1 (Dog), the formula on C1 becomes "=if(A1=B2,1,0)".
Is there a way that the formula will still compare the original 2 cells even
if I moved the data down?? That is, the formula on C1 will still be
"=if(A1=B1,1,0)"


Thanks.

"IF" function question
 

Try this in C1:

=--(INDIRECT("A1")=INDIRECT("B1"))

That will *always* refer to A1 and B1

--
Biff
Microsoft Excel MVP


"Lorderon" wrote in message
...
Hi,
I have an IF function comparing 2 cells and it is under column C:

A B C
cat Dog "=if(A1=B1,1,0)" (here result is 0)
Dog bird "=if(A2=B2,1,0)" (here result is 0)

Now, every time the result is ZERO in column C, I will move the
corresponding data on column B downwards "Insert - Shift cells down". The
problem here is that my formula on column C also moves downwards! So if I
moved down the data on B1 (Dog), the formula on C1 becomes
"=if(A1=B2,1,0)".
Is there a way that the formula will still compare the original 2 cells
even
if I moved the data down?? That is, the formula on C1 will still be
"=if(A1=B1,1,0)"


Thanks.

"IF" function question
 

Hi,
Thanks for your reply, but unfortunately this didn't. Pls take note the A1
and B1 are Cell locations.

Let me explain further what I'm doing....

I'm comparing the data of column A and column B, if they are the same the
value on column C will be 1 else it will be 0.

So if I found out that the value on Col A and Col B are not the same
(meaning vale on Col C is 0), I will "shift down" the data on Col B (the
different one only) to match Col A. The problem here is that, the formula on
Col C is shifted as well. So for example I shifted the data on cell location
B1 downwards the formula on cell location C1 becomes "=if(A1=B2,1,0)". I want
it to be still the same as the original.

ORIGINAL DATA:
Col A Col B Col C
cat Dog "=if(A1=B1,1,0)", result is 0
Dog bird "=if(A2=B2,1,0)", result is 0
bird eat "=if(A3=B3,1,0)", result is 0

So, I looked for the data which are different and match them together, so I
"moved down" the data on Col B to match Col A, so it becomes....

SHIFTED DATA:
Col A Col B Col C (all the formula changed!!)
cat "=if(A1=B2,1,0)", result is 0
Dog Dog "=if(A2=B3,1,0)", result is 0
bird bird "=if(A3=B4,1,0)", result is 0
eat


As you can see the formula on Col C also shifted, if they maintained my
original formula the values on cell C1 is still 0, value on cell C2 will be
1, value on cell C3 will be 1. But now they are all 0 because the formula
shifted after I moved down the data on cell location B1.


"T. Valko" wrote:

Try this in C1:

=--(INDIRECT("A1")=INDIRECT("B1"))

That will *always* refer to A1 and B1

--
Biff
Microsoft Excel MVP


"Lorderon" wrote in message
...
Hi,
I have an IF function comparing 2 cells and it is under column C:

A B C
cat Dog "=if(A1=B1,1,0)" (here result is 0)
Dog bird "=if(A2=B2,1,0)" (here result is 0)

Now, every time the result is ZERO in column C, I will move the
corresponding data on column B downwards "Insert - Shift cells down". The
problem here is that my formula on column C also moves downwards! So if I
moved down the data on B1 (Dog), the formula on C1 becomes
"=if(A1=B2,1,0)".
Is there a way that the formula will still compare the original 2 cells
even
if I moved the data down?? That is, the formula on C1 will still be
"=if(A1=B1,1,0)"


Thanks.

"IF" function question
 

The formula I suggested does what you want but it's limited to those EXACT
cells only.

Try this one in C1 and copy down as needed:

=--(OFFSET(C1,,-2)=OFFSET(C1,,-1))

--
Biff
Microsoft Excel MVP


"Lorderon" wrote in message
...
Hi,
Thanks for your reply, but unfortunately this didn't. Pls take note the A1
and B1 are Cell locations.

Let me explain further what I'm doing....

I'm comparing the data of column A and column B, if they are the same the
value on column C will be 1 else it will be 0.

So if I found out that the value on Col A and Col B are not the same
(meaning vale on Col C is 0), I will "shift down" the data on Col B (the
different one only) to match Col A. The problem here is that, the formula
on
Col C is shifted as well. So for example I shifted the data on cell
location
B1 downwards the formula on cell location C1 becomes "=if(A1=B2,1,0)". I
want
it to be still the same as the original.

ORIGINAL DATA:
Col A Col B Col C
cat Dog "=if(A1=B1,1,0)", result is 0
Dog bird "=if(A2=B2,1,0)", result is 0
bird eat "=if(A3=B3,1,0)", result is 0

So, I looked for the data which are different and match them together, so
I
"moved down" the data on Col B to match Col A, so it becomes....

SHIFTED DATA:
Col A Col B Col C (all the formula changed!!)
cat "=if(A1=B2,1,0)", result is 0
Dog Dog "=if(A2=B3,1,0)", result is 0
bird bird "=if(A3=B4,1,0)", result is 0
eat


As you can see the formula on Col C also shifted, if they maintained my
original formula the values on cell C1 is still 0, value on cell C2 will
be
1, value on cell C3 will be 1. But now they are all 0 because the formula
shifted after I moved down the data on cell location B1.


"T. Valko" wrote:

Try this in C1:

=--(INDIRECT("A1")=INDIRECT("B1"))

That will *always* refer to A1 and B1

--
Biff
Microsoft Excel MVP


"Lorderon" wrote in message
...
Hi,
I have an IF function comparing 2 cells and it is under column C:

A B C
cat Dog "=if(A1=B1,1,0)" (here result is 0)
Dog bird "=if(A2=B2,1,0)" (here result is 0)

Now, every time the result is ZERO in column C, I will move the
corresponding data on column B downwards "Insert - Shift cells down".
The
problem here is that my formula on column C also moves downwards! So if
I
moved down the data on B1 (Dog), the formula on C1 becomes
"=if(A1=B2,1,0)".
Is there a way that the formula will still compare the original 2 cells
even
if I moved the data down?? That is, the formula on C1 will still be
"=if(A1=B1,1,0)"


Thanks.

"IF" function question
 

Hi,
It finally worked! Thanks!


"T. Valko" wrote:

The formula I suggested does what you want but it's limited to those EXACT
cells only.

Try this one in C1 and copy down as needed:

=--(OFFSET(C1,,-2)=OFFSET(C1,,-1))

--
Biff
Microsoft Excel MVP


"Lorderon" wrote in message
...
Hi,
Thanks for your reply, but unfortunately this didn't. Pls take note the A1
and B1 are Cell locations.

Let me explain further what I'm doing....

I'm comparing the data of column A and column B, if they are the same the
value on column C will be 1 else it will be 0.

So if I found out that the value on Col A and Col B are not the same
(meaning vale on Col C is 0), I will "shift down" the data on Col B (the
different one only) to match Col A. The problem here is that, the formula
on
Col C is shifted as well. So for example I shifted the data on cell
location
B1 downwards the formula on cell location C1 becomes "=if(A1=B2,1,0)". I
want
it to be still the same as the original.

ORIGINAL DATA:
Col A Col B Col C
cat Dog "=if(A1=B1,1,0)", result is 0
Dog bird "=if(A2=B2,1,0)", result is 0
bird eat "=if(A3=B3,1,0)", result is 0

So, I looked for the data which are different and match them together, so
I
"moved down" the data on Col B to match Col A, so it becomes....

SHIFTED DATA:
Col A Col B Col C (all the formula changed!!)
cat "=if(A1=B2,1,0)", result is 0
Dog Dog "=if(A2=B3,1,0)", result is 0
bird bird "=if(A3=B4,1,0)", result is 0
eat


As you can see the formula on Col C also shifted, if they maintained my
original formula the values on cell C1 is still 0, value on cell C2 will
be
1, value on cell C3 will be 1. But now they are all 0 because the formula
shifted after I moved down the data on cell location B1.


"T. Valko" wrote:

Try this in C1:

=--(INDIRECT("A1")=INDIRECT("B1"))

That will *always* refer to A1 and B1

--
Biff
Microsoft Excel MVP


"Lorderon" wrote in message
...
Hi,
I have an IF function comparing 2 cells and it is under column C:

A B C
cat Dog "=if(A1=B1,1,0)" (here result is 0)
Dog bird "=if(A2=B2,1,0)" (here result is 0)

Now, every time the result is ZERO in column C, I will move the
corresponding data on column B downwards "Insert - Shift cells down".
The
problem here is that my formula on column C also moves downwards! So if
I
moved down the data on B1 (Dog), the formula on C1 becomes
"=if(A1=B2,1,0)".
Is there a way that the formula will still compare the original 2 cells
even
if I moved the data down?? That is, the formula on C1 will still be
"=if(A1=B1,1,0)"


Thanks.

"IF" function question
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lorderon" wrote in message
...
Hi,
It finally worked! Thanks!


"T. Valko" wrote:

The formula I suggested does what you want but it's limited to those
EXACT
cells only.

Try this one in C1 and copy down as needed:

=--(OFFSET(C1,,-2)=OFFSET(C1,,-1))

--
Biff
Microsoft Excel MVP


"Lorderon" wrote in message
...
Hi,
Thanks for your reply, but unfortunately this didn't. Pls take note the
A1
and B1 are Cell locations.

Let me explain further what I'm doing....

I'm comparing the data of column A and column B, if they are the same
the
value on column C will be 1 else it will be 0.

So if I found out that the value on Col A and Col B are not the same
(meaning vale on Col C is 0), I will "shift down" the data on Col B
(the
different one only) to match Col A. The problem here is that, the
formula
on
Col C is shifted as well. So for example I shifted the data on cell
location
B1 downwards the formula on cell location C1 becomes "=if(A1=B2,1,0)".
I
want
it to be still the same as the original.

ORIGINAL DATA:
Col A Col B Col C
cat Dog "=if(A1=B1,1,0)", result is 0
Dog bird "=if(A2=B2,1,0)", result is 0
bird eat "=if(A3=B3,1,0)", result is 0

So, I looked for the data which are different and match them together,
so
I
"moved down" the data on Col B to match Col A, so it becomes....

SHIFTED DATA:
Col A Col B Col C (all the formula changed!!)
cat "=if(A1=B2,1,0)", result is 0
Dog Dog "=if(A2=B3,1,0)", result is 0
bird bird "=if(A3=B4,1,0)", result is 0
eat


As you can see the formula on Col C also shifted, if they maintained my
original formula the values on cell C1 is still 0, value on cell C2
will
be
1, value on cell C3 will be 1. But now they are all 0 because the
formula
shifted after I moved down the data on cell location B1.


"T. Valko" wrote:

Try this in C1:

=--(INDIRECT("A1")=INDIRECT("B1"))

That will *always* refer to A1 and B1

--
Biff
Microsoft Excel MVP


"Lorderon" wrote in message
...
Hi,
I have an IF function comparing 2 cells and it is under column C:

A B C
cat Dog "=if(A1=B1,1,0)" (here result is 0)
Dog bird "=if(A2=B2,1,0)" (here result is 0)

Now, every time the result is ZERO in column C, I will move the
corresponding data on column B downwards "Insert - Shift cells
down".
The
problem here is that my formula on column C also moves downwards! So
if
I
moved down the data on B1 (Dog), the formula on C1 becomes
"=if(A1=B2,1,0)".
Is there a way that the formula will still compare the original 2
cells
even
if I moved the data down?? That is, the formula on C1 will still be
"=if(A1=B1,1,0)"


Thanks.