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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
442
6StringJazzer
6StringJazzer
S
  • Question Question
Big, Multi-Step Macro for Efficiently Cutting Big Pieces Into Small Pieces
Replies
1
Views
462
Solon Aquila
S
I
  • Question Question
Divide cell value so weekly stock is balance between warehouses
Replies
1
Views
210
Kaper
Kaper
R
  • Question Question
Print handouts for multiple employees that use different values
Replies
3
Views
353
jolivanes
jolivanes
wizau
  • Solved
Nested IF statements, or a better way?
Replies
4
Views
246
wizau
wizau

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

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