Take value of first cell then subtract that from every cell for different materials

K

Kulo5856

New Member
Joined
May 11, 2020
Messages
36
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
1589183730934.png

In this image i need to get the stock will reduce from the remaining stock value 28 then ( 28 - 5)= 23, (23-3)= 20, like this for various materials
Exact take value of first cell then subtract that from every cell and also i have to apply this formula for all materials.
Please help
 
Sort by date Sort by votes
Hi again,

So here goes nothing (as I'm puzzled by some of the dates and their meaning). I'm assuming "OB" is the starting point and I would keep this list static in terms of quantities. Perhaps add a column "Outstanding Balance". I apologize up front if this is not what you are after. Bear with, be patient then.

Outstanding: =SUMIF(OB!$C:$C,Outward!$C2,OB!$E:$E)
StockIn: =SUMIFS('GRN SRN'!$I:$I,'GRN SRN'!$C:$C,Outward!$C2,'GRN SRN'!$B:$B,"<="&Outward!$B2)
StockOut =SUMIF(Outward!$C$2:$C2,Outward!$C2,Outward!$G$2:$G2)
Remaining Stock: =Outstanding+StockIn-StockOut
Book1
ABCDEFG
1###Posting DatePart NoDescriptionOB QtyUOMOB
211/04/2020119-4151Screw Special (57-4910)10Nos19
321/04/202093-4264Bedknife-Low Cut.50Nos77
431/04/202092-9218V belt5Nos48
OB
Cell Formulas
RangeFormula
G2:G4G2=E2+SUMIF('GRN SRN'!$C:$C,$C2,'GRN SRN'!$I:$I)-SUMIF(Outward!$C:$C,C2,Outward!$G:$G)

Book1
ABCDEFGHIJK
1###Posting DatePart NoDescriptionCustomer / Vendor NameOut QtyUOMRemaining StockSerial NoRemarks
212/05/2020119-4151Screw Special (57-4910)XXXXX2Nos28
324/05/202093-4264Bedknife-Low Cut.XXXXX3Nos86
434/05/202092-9218V beltXXXXX1Nos49
542/05/2020119-4151Screw Special (57-4910)XXXXX5Nos23
654/05/202093-4264Bedknife-Low Cut.XXXXX3Nos83
762/05/2020119-4151Screw Special (57-4910)XXXXX3Nos20
874/05/202093-4264Bedknife-Low Cut.XXXXX3Nos80
982/05/2020119-4151Screw Special (57-4910)XXXXX1Nos19
1094/05/202093-4264Bedknife-Low Cut.XXXXX3Nos77
11104/05/202092-9218V beltXXXXX1Nos48
Outward
Cell Formulas
RangeFormula
I2:I11I2=Outstanding+StockIn-StockOut

Book1
ABCDEFGHIJ
1###Posting DatePart NoDescriptionSender / Supplier NameDocument NoGRN NoSRN NoIn QtyUOM
2125/04/2020119-4151Screw Special (57-4910)XXXXXXXXXXXXXX20Nos
3225/04/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX9Nos
4325/04/202092-9218V beltXXXXXXXXXXXXXX15Nos
543/05/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX1Nos
6525/04/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX9Nos
7625/04/202092-9218V beltXXXXXXXXXXXXXX15Nos
873/05/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX1Nos
9825/04/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX9Nos
10925/04/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX9Nos
111025/04/202092-9218V beltXXXXXXXXXXXXXX15Nos
12113/05/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX1Nos
GRN SRN
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
GraH said:
Hi again,

So here goes nothing (as I'm puzzled by some of the dates and their meaning). I'm assuming "OB" is the starting point and I would keep this list static in terms of quantities. Perhaps add a column "Outstanding Balance". I apologize up front if this is not what you are after. Bear with, be patient then.

Outstanding: =SUMIF(OB!$C:$C,Outward!$C2,OB!$E:$E)
StockIn: =SUMIFS('GRN SRN'!$I:$I,'GRN SRN'!$C:$C,Outward!$C2,'GRN SRN'!$B:$B,"<="&Outward!$B2)
StockOut =SUMIF(Outward!$C$2:$C2,Outward!$C2,Outward!$G$2:$G2)
Remaining Stock: =Outstanding+StockIn-StockOut
Book1
ABCDEFG
1###Posting DatePart NoDescriptionOB QtyUOMOB
211/04/2020119-4151Screw Special (57-4910)10Nos19
321/04/202093-4264Bedknife-Low Cut.50Nos77
431/04/202092-9218V belt5Nos48
OB
Cell Formulas
RangeFormula
G2:G4G2=E2+SUMIF('GRN SRN'!$C:$C,$C2,'GRN SRN'!$I:$I)-SUMIF(Outward!$C:$C,C2,Outward!$G:$G)

Book1
ABCDEFGHIJK
1###Posting DatePart NoDescriptionCustomer / Vendor NameOut QtyUOMRemaining StockSerial NoRemarks
212/05/2020119-4151Screw Special (57-4910)XXXXX2Nos28
324/05/202093-4264Bedknife-Low Cut.XXXXX3Nos86
434/05/202092-9218V beltXXXXX1Nos49
542/05/2020119-4151Screw Special (57-4910)XXXXX5Nos23
654/05/202093-4264Bedknife-Low Cut.XXXXX3Nos83
762/05/2020119-4151Screw Special (57-4910)XXXXX3Nos20
874/05/202093-4264Bedknife-Low Cut.XXXXX3Nos80
982/05/2020119-4151Screw Special (57-4910)XXXXX1Nos19
1094/05/202093-4264Bedknife-Low Cut.XXXXX3Nos77
11104/05/202092-9218V beltXXXXX1Nos48
Outward
Cell Formulas
RangeFormula
I2:I11I2=Outstanding+StockIn-StockOut

Book1
ABCDEFGHIJ
1###Posting DatePart NoDescriptionSender / Supplier NameDocument NoGRN NoSRN NoIn QtyUOM
2125/04/2020119-4151Screw Special (57-4910)XXXXXXXXXXXXXX20Nos
3225/04/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX9Nos
4325/04/202092-9218V beltXXXXXXXXXXXXXX15Nos
543/05/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX1Nos
6525/04/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX9Nos
7625/04/202092-9218V beltXXXXXXXXXXXXXX15Nos
873/05/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX1Nos
9825/04/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX9Nos
10925/04/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX9Nos
111025/04/202092-9218V beltXXXXXXXXXXXXXX15Nos
12113/05/202093-4264Bedknife-Low Cut.XXXXXXXXXXXXXX1Nos
GRN SRN
Click to expand...

Thanks for your support, but i can't get answer from the formula.
Please check with the data entered

Sample Stock Ledger - Copy.xlsx
ABCDEFGHIJK
1###Posting DatePart NoDescriptionCustomer / Vendor NameDocument NoOut QtyUOMRemaining StockSerial NoRemarks
2102/05/2020119-4151Screw Special (57-4910)XXXXXXXX2Nos8
3204/05/202093-4264Bedknife-Low Cut.XXXXXXXX3Nos47
4304/05/202092-9218V beltXXXXXXXX1Nos4
5402/05/2020119-4151Screw Special (57-4910)XXXXXXXX5Nos3
6504/05/202093-4264Bedknife-Low Cut.XXXXXXXX3Nos44
7602/05/2020119-4151Screw Special (57-4910)XXXXXXXX3Nos0
8704/05/202093-4264Bedknife-Low Cut.XXXXXXXX3Nos41
9802/05/2020119-4151Screw Special (57-4910)XXXXXXXX1Nos-1
10904/05/202093-4264Bedknife-Low Cut.XXXXXXXX3Nos38
111004/05/202092-9218V beltXXXXXXXX1Nos3
12 
13
14
15Outstanding 10
16Stock in0
17Stock out2
OUTWARD
Cell Formulas
RangeFormula
I2:I11I2=SUMIF(OB!C:C,OUTWARD!$C2,OB!$E:$E)+SUMIFS('GRN SRN'!$I:$I,'GRN SRN'!$C:$C,OUTWARD!$C2,'GRN SRN'!B:B,"<="&OUTWARD!$B2)-SUMIF(OUTWARD!$C$2:$C2,OUTWARD!$C2,OUTWARD!$G$2:$G2)
D2:D12D2=IFERROR(VLOOKUP(C:C,Dump!B:C,2,0),"")
G15G15=SUMIF(OB!C:C,OUTWARD!$C2,OB!$E:$E)
G16G16=SUMIFS('GRN SRN'!$C:$C,'GRN SRN'!$I:$I,OUTWARD!$C2,'GRN SRN'!$B:$B,"<="&OUTWARD!$B2)
G17G17=SUMIF(OUTWARD!$C$2:$C2,OUTWARD!$C2,OUTWARD!$G$2:$G2)
Cells with Data Validation
CellAllowCriteria
C2:C11List=Dump!$B$2:$B$600


Sample Stock Ledger - Copy.xlsx
ABCDEFG
1###Posting DatePart NoDescriptionOB QtyUOMCount
2101/04/2020119-4151Screw Special (57-4910)10Nos19
3201/04/202093-4264Bedknife-Low Cut.50Nos77
4301/04/202092-9218V belt5Nos48
5 
OB
Cell Formulas
RangeFormula
G2:G4G2=E2+SUMIF('GRN SRN'!$C:$C,$C2,'GRN SRN'!$I:$I)-SUMIF(OUTWARD!$C:$C,C2,OUTWARD!$G:$G)
D2:D5D2=IFERROR(VLOOKUP(C:C,Dump!B:C,2,0),"")
Cells with Data Validation
CellAllowCriteria
C2:C5List=Dump!$B$2:$B$600
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Formula to Auto Subtract Cells That Change Weekly
Replies
4
Views
89
6StringJazzer
6StringJazzer
R
  • Question
Print handouts for multiple employees that use different values
Replies
3
Views
122
jolivanes
jolivanes
M
  • Question
Can Solver Save My Sanity?
Replies
2
Views
256
mickyd67
M
S
  • Solved
How to Rotate Rows to Columns with Large Data Set
Replies
8
Views
233
kevin9999
K
K
  • Question
Array SUM or SUMPRODUCT function error
Replies
1
Views
229
KratosZG
K

Forum statistics

Threads
1,214,874
Messages
6,122,034
Members
449,061
Latest member
TheRealJoaquin

Share this page

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