If sheet 2 cell b2= sheet 3 cell b2 and not 0 then show in sheet 1 cell b2 the number of cell b2 in sheeet 2 or sheet 3

[stavrosiona]

Hello,
I have an issue with a code in this excel file I have done from the past.


Basically I have sheet 1 which is doing the deduction of cells in sheet 2 and sheet 3. i.e Sheet 1 = Pending Sheet 2 = Order, Sheet 3 =Deliver


So cell Pending cell B3 in sheet 1 is the pending amount of the order -deliberies and i am using in sheet 1 the code: =(ORDER!B3-DELIVER!B3)

What I want is that to use this code but when (ORDER!B3 = DELIVER!B3) and is not 0 then to write in cell B3 in pending the number of order or deliver.


The reason is that I use conditional formatiing to fill yellow colour the cell B3 is Order!B3= Deliver!B3 but i want instead of empty cell to write the number of order or deliver (which is the same)
So for example:
Order 5 Deliver 3 The Pending cell shows 2 with grey fill in (I used conditional when Order>Deliver to fill it grey)
Order 5 Deliver 5 The pending cell shows empry (use vba) and filled with yellow (used conditional whn Order=Deliver to be yellow if not 0)
Thanks,

 

[oldbrewer]

[TABLE="width: 714"]
<colgroup><col><col span="10"></colgroup><tbody>[TR]
[TD]sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sheet2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sheet3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]one is zero[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]one is zero[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formula in A2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]=IF(OR(F2=0,J2=0),"one is zero",F2-J2)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[stavrosiona]

dear oldbrewer,
Thanks for the answer. Sorry I was not clear, what I need is the following:
Lets say in Sheet 2 in Cell A3 I have the number 5 and in Sheet 3 in Cell A3 I have the number 4 so

Now in Sheet 1 for example in cell a3 I have this formula: =(sheet2!a3-sheet3!a3)
So it shows me the mathematica calculation of sheet 2 same cell minus (-) sheet 3 same cell.
Now what I want is to show me that number i.e 5-4=1. By the formula I am using is ok.
But I want when:
Lets say in Sheet 2 in Cell A3 I have the number 5 and in Sheet 3 in Cell A3 I have the number 5 so

Normally with my code the result should be 0. But instead of 0 I want to show me 5 (which is the number of either sheet 2 or sheet 3 because is the same).
So basically I want to do the sheet2-sheet3 but when the answer is 0 then tio show me the number of one of the cells of a3 (sheet 2 or sheet 3) so to show number 5 instead of 0

Thanks.

 

[oldbrewer]

if(sheet2!a3-sheet3!a3=0,sheet2!a3,sheet2!a3-sheet3!a3)