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

New Member
Joined
Apr 17, 2015
Messages
22
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,
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
[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]
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top