a simple excel, I have 4 columns in my excel

A is for checking
B is the price or product
C is profit
D is cost

what i want to do is simple, I set formular in C & D, once a user
input either C or D, the another cell will show the different. i.e.

if C is input, D=B-C
if D is input, C=B-D

then A is checking purpose, if either C or D is inputted, it will
shown "Y"

but if I use IF(D0, B-D, "") it will introduce cyclic reference.

any solution for this?
Thanks.

Why not use
=IF(AND(C1="",D1="","","Y)

Also what you have given will not create cyclical reference
"Edward" wrote:

a simple excel, I have 4 columns in my excel

A is for checking
B is the price or product
C is profit
D is cost

what i want to do is simple, I set formular in C & D, once a user
input either C or D, the another cell will show the different. i.e.

if C is input, D=B-C
if D is input, C=B-D

then A is checking purpose, if either C or D is inputted, it will
shown "Y"

but if I use IF(D0, B-D, "") it will introduce cyclic reference.

any solution for this?
Thanks.

Thanks for replying, I did use your formula but the problem is it did
cyclic reference,

because the formula in C is
=IF(D20,B2-D2,"")

formula in D is
=IF(C20,B2-C2,"")

when i did this all value in D is not blank, but 0 (and say i have
cyclic reference)

D reference to C and C reference to D. I can't attached my file here
so can't show you the template.

Thanks.


On 11$B7n(B10$BF|(B, $B2<8a(B2$B;~(B53$BJ,(B, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
Why not use
=IF(AND(C1="",D1="","","Y)

Also what you have given will not create cyclical reference

"Edward" wrote:
a simple excel, I have 4 columns in my excel

A is for checking
B is the price or product
C is profit
D is cost

what i want to do is simple, I set formular in C & D, once a user
input either C or D, the another cell will show the different. i.e.

if C is input, D=B-C
if D is input, C=B-D

then A is checking purpose, if either C or D is inputted, it will
shown "Y"

but if I use IF(D0, B-D, "") it will introduce cyclic reference.

any solution for this?
Thanks.

Use this in E2
=IF(C20,B2-C2,IF(D20,B2-D2,""))

"Edward" wrote:

Thanks for replying, I did use your formula but the problem is it did
cyclic reference,

because the formula in C is
=IF(D20,B2-D2,"")

formula in D is
=IF(C20,B2-C2,"")

when i did this all value in D is not blank, but 0 (and say i have
cyclic reference)

D reference to C and C reference to D. I can't attached my file here
so can't show you the template.

Thanks.


On 11月10日, 下�2時53分, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
Why not use
=IF(AND(C1="",D1="","","Y)

Also what you have given will not create cyclical reference

"Edward" wrote:
a simple excel, I have 4 columns in my excel

A is for checking
B is the price or product
C is profit
D is cost

what i want to do is simple, I set formular in C & D, once a user
input either C or D, the another cell will show the different. i.e.

if C is input, D=B-C
if D is input, C=B-D

then A is checking purpose, if either C or D is inputted, it will
shown "Y"

but if I use IF(D0, B-D, "") it will introduce cyclic reference.

any solution for this?
Thanks.

Yes, but since B,C,D are prices, profit and cost accordingly, I assume
we know only profit or cost and automatically determine the other
without setting the formula myself. so I stick it to C & D. E would be
meaningless somehow (since I don't know whether it represents profit
or cost)

Thanks again.



On 11$B7n(B10$BF|(B, $B2<8a(B3$B;~(B13$BJ,(B, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
Use this in E2
=IF(C20,B2-C2,IF(D20,B2-D2,""))

"Edward" wrote:
Thanks for replying, I did use your formula but the problem is it did
cyclic reference,

because the formula in C is
=IF(D20,B2-D2,"")

formula in D is
=IF(C20,B2-C2,"")

when i did this all value in D is not blank, but 0 (and say i have
cyclic reference)

D reference to C and C reference to D. I can't attached my file here
so can't show you the template.

Thanks.

On 11$B7n(B10$BF|(B, $B2<8a(B2$B;~(B53$BJ,(B, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
Why not use
=IF(AND(C1="",D1="","","Y)

Also what you have given will not create cyclical reference

"Edward" wrote:
a simple excel, I have 4 columns in my excel

A is for checking
B is the price or product
C is profit
D is cost

what i want to do is simple, I set formular in C & D, once a user
input either C or D, the another cell will show the different. i.e.

if C is input, D=B-C
if D is input, C=B-D

then A is checking purpose, if either C or D is inputted, it will
shown "Y"

but if I use IF(D0, B-D, "") it will introduce cyclic reference.

any solution for this?
Thanks.

You have to use another column to calculate C & D...

If you use Col A to indicate whether you have Profit or Cost then you can
have formulae in C & D

"Edward" wrote:

Yes, but since B,C,D are prices, profit and cost accordingly, I assume
we know only profit or cost and automatically determine the other
without setting the formula myself. so I stick it to C & D. E would be
meaningless somehow (since I don't know whether it represents profit
or cost)

Thanks again.



On 11月10日, 下�3時13分, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
Use this in E2
=IF(C20,B2-C2,IF(D20,B2-D2,""))

"Edward" wrote:
Thanks for replying, I did use your formula but the problem is it did
cyclic reference,

because the formula in C is
=IF(D20,B2-D2,"")

formula in D is
=IF(C20,B2-C2,"")

when i did this all value in D is not blank, but 0 (and say i have
cyclic reference)

D reference to C and C reference to D. I can't attached my file here
so can't show you the template.

Thanks.

On 11月10日, 下�2時53分, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
Why not use
=IF(AND(C1="",D1="","","Y)

Also what you have given will not create cyclical reference

"Edward" wrote:
a simple excel, I have 4 columns in my excel

A is for checking
B is the price or product
C is profit
D is cost

what i want to do is simple, I set formular in C & D, once a user
input either C or D, the another cell will show the different. i.e.

if C is input, D=B-C
if D is input, C=B-D

then A is checking purpose, if either C or D is inputted, it will
shown "Y"

but if I use IF(D0, B-D, "") it will introduce cyclic reference.

any solution for this?
Thanks.

so far I have the same conclusion as yours, but not sure if there are
"smarter" ways.

anyway, great thanks for your advise.

On 11$B7n(B10$BF|(B, $B2<8a(B3$B;~(B53$BJ,(B, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
You have to use another column to calculate C & D...

If you use Col A to indicate whether you have Profit or Cost then you can
have formulae in C & D

"Edward" wrote:
Yes, but since B,C,D are prices, profit and cost accordingly, I assume
we know only profit or cost and automatically determine the other
without setting the formula myself. so I stick it to C & D. E would be
meaningless somehow (since I don't know whether it represents profit
or cost)

Thanks again.

On 11$B7n(B10$BF|(B, $B2<8a(B3$B;~(B13$BJ,(B, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
Use this in E2
=IF(C20,B2-C2,IF(D20,B2-D2,""))

"Edward" wrote:
Thanks for replying, I did use your formula but the problem is it did
cyclic reference,

because the formula in C is
=IF(D20,B2-D2,"")

formula in D is
=IF(C20,B2-C2,"")

when i did this all value in D is not blank, but 0 (and say i have
cyclic reference)

D reference to C and C reference to D. I can't attached my file here
so can't show you the template.

Thanks.

On 11$B7n(B10$BF|(B, $B2<8a(B2$B;~(B53$BJ,(B, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
Why not use
=IF(AND(C1="",D1="","","Y)

Also what you have given will not create cyclical reference

"Edward" wrote:
a simple excel, I have 4 columns in my excel

A is for checking
B is the price or product
C is profit
D is cost

what i want to do is simple, I set formular in C & D, once a user
input either C or D, the another cell will show the different. i.e.

if C is input, D=B-C
if D is input, C=B-D

then A is checking purpose, if either C or D is inputted, it will
shown "Y"

but if I use IF(D0, B-D, "") it will introduce cyclic reference.

any solution for this?
Thanks.

for reference, finally archieved by using macro of
on_worksheetchange_selection to accomplish, thanks.


On 11$B7n(B10$BF|(B, $B2<8a(B4$B;~(B00$BJ,(B, Edward wrote:
so far I have the same conclusion as yours, but not sure if there are
"smarter" ways.

anyway, great thanks for your advise.

On 11$B7n(B10$BF|(B, $B2<8a(B3$B;~(B53$BJ,(B, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &

"hotmail.com" wrote:
You have to use another column to calculate C & D...

If you use Col A to indicate whether you have Profit or Cost then you can
have formulae in C & D

"Edward" wrote:
Yes, but since B,C,D are prices, profit and cost accordingly, I assume
we know only profit or cost and automatically determine the other
without setting the formula myself. so I stick it to C & D. E would be
meaningless somehow (since I don't know whether it represents profit
or cost)

Thanks again.

On 11$B7n(B10$BF|(B, $B2<8a(B3$B;~(B13$BJ,(B, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
Use this in E2
=IF(C20,B2-C2,IF(D20,B2-D2,""))

"Edward" wrote:
Thanks for replying, I did use your formula but the problem is it did
cyclic reference,

because the formula in C is
=IF(D20,B2-D2,"")

formula in D is
=IF(C20,B2-C2,"")

when i did this all value in D is not blank, but 0 (and say i have
cyclic reference)

D reference to C and C reference to D. I can't attached my file here
so can't show you the template.

Thanks.

On 11$B7n(B10$BF|(B, $B2<8a(B2$B;~(B53$BJ,(B, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
Why not use
=IF(AND(C1="",D1="","","Y)

Also what you have given will not create cyclical reference

"Edward" wrote:
a simple excel, I have 4 columns in my excel

A is for checking
B is the price or product
C is profit
D is cost

what i want to do is simple, I set formular in C & D, once a user
input either C or D, the another cell will show the different. i.e.

if C is input, D=B-C
if D is input, C=B-D

then A is checking purpose, if either C or D is inputted, it will
shown "Y"

but if I use IF(D0, B-D, "") it will introduce cyclic reference.

any solution for this?
Thanks.