Daily inventory follow up - opening stock = to closing stock previous day if....

[Philippevr]

Hi,

I have a simple worksheet with daily stock entry and withdrawal for different items (based on a validation list).

Column A = product description (items from the validation data list)
Column B = Opening stock
Column C = Withdrawal stock
Column D = Closing stock

I want to find automatically the opening stock based on the previous closing stock after I have selected the item.

Any suggestions please?

Phil.

 

[footoo]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim obal As Range
If Target.Count <> 1 Then Exit Sub Else If Target.Column <> 1 Then Exit Sub
Set obal = [A:A].Find(What:=Target, After:=Target, LookAt:=xlWhole, SearchDirection:=xlPrevious)(1, 4)
Application.EnableEvents = False
If Not obal Is Nothing Then Target(1, 2) = obal.Value
Application.EnableEvents = True
End Sub

 

[Philippevr]

Footoo, thank you for your reply. I am not really familiar with VBA. I was hoping to find a more conventional solution with the function tools. I have been watching some video's about the last occurrence with Vlookup but without success.

 

[footoo]

Footoo, thank you for your reply. I am not really familiar with VBA. I was hoping to find a more conventional solution with the function tools. I have been watching some video's about the last occurrence with Vlookup but without success.

https://www.exceltip.com/excel-formula-and-function/vlookup-to-find-last-matching-value.html

 

[Philippevr]

Hi Footoo, thank you again. I had already seen this tip. So I used column B "Opening stock level" this formula "lookup(2,1/($A$2:A2=A1),$E$2:E2. As you noticed I changed up the lookup vector formula. Is this an appropriate way or is there another possibility? The first cell in column B (B2) will have a #DIV/0! because B1 has a non value hence can be solve with iferror or another formulation. Any suggestions? Or is this acceptable?

PVR
[TABLE="width: 400"]
<tbody>[TR]
[TD="align: right"]Description[/TD]
[TD="align: right"]Opening Stock Level[/TD]
[TD="align: right"]Reception[/TD]
[TD="align: right"]Withdrawal[/TD]
[TD="align: right"]Stock Final Level (end of the day)[/TD]
[/TR]
[TR]
[TD="align: right"]Sugar[/TD]
[TD="align: right"]1,500 [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1,000[/TD]
[/TR]
[TR]
[TD="align: right"]Corn[/TD]
[TD="align: right"]55000[/TD]
[TD="align: right"]14000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]69,000[/TD]
[/TR]
[TR]
[TD="align: right"]Corn[/TD]
[TD="align: right"]69,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8,000[/TD]
[TD="align: right"]61,000[/TD]
[/TR]
[TR]
[TD="align: right"]Sugar[/TD]
[TD="align: right"]1,000[/TD]
[TD="align: right"]20,000[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]16,000[/TD]
[/TR]
[TR]
[TD="align: right"]Salt[/TD]
[TD="align: right"]30,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30,000[/TD]
[/TR]
[TR]
[TD="align: right"]Salt[/TD]
[TD="align: right"]30,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2,500[/TD]
[TD="align: right"]27,500[/TD]
[/TR]
[TR]
[TD="align: right"]Corn[/TD]
[TD="align: right"]61,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14,000[/TD]
[TD="align: right"]47,000[/TD]
[/TR]
[TR]
[TD="align: right"]Sugar[/TD]
[TD="align: right"]16,000[/TD]
[TD="align: right"]2,000[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]15,000
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]This column B must have an automatic fill based on the previous final stock level hence I used the Lookup formula[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Philippevr]

I just tried it after my previous post but doesn't work so far....

 

[Peter_SSs]

See if this is any use.

Excel Workbook
	A	B	C	D	E
1	Description	Opening Stock Level	Reception	Withdrawal	Stock Final
2	Sugar	Unknown	0	500	1,000
3	Corn	Unknown	14000	0	69,000
4	Corn	69000	0	8,000	61,000
5	Sugar	1000	20,000	5,000	16,000
6	Salt	Unknown	0	0	30,000
7	Salt	30000	0	2,500	27,500
8	Corn	61000	0	14,000	47,000
9	Sugar	16000	2,000	3,000	15,000
Stock

.. or if the formula is to be pre-populated further down the column, try

=IF(A2="","",IFERROR(INDEX(E$1:E1,AGGREGATE(14,6,(ROW(E$1:E1)-ROW(E$1)+1)/(A$1:A1=A2),1)),E2+D2-C2))

 

[Philippevr]

Thank you so much Peter. I am very impressed and I used the second option. As you noticed cleary, I needed to populate all the other cells in the same column E. Once again I am amazed. A question: after the aggregate function you put 14,6; are these function numbers for Large and Product? That's quit some rocket science here.

Stock

	A	B	C	D	E
Description	Opening Stock Level
Sugar	Unknown
Corn	Unknown
Corn
Sugar
Salt	Unknown
Salt
Corn
Sugar

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:84px;"><col style="width:140px;"><col style="width:77px;"><col style="width:83px;"><col style="width:85px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]Reception[/TD]
[TD="align: right"]Withdrawal[/TD]
[TD="align: right"]Stock Final[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1,000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]14000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]69,000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]69000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8,000[/TD]
[TD="align: right"]61,000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]20,000[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]16,000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30,000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]30000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2,500[/TD]
[TD="align: right"]27,500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]61000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14,000[/TD]
[TD="align: right"]47,000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]16000[/TD]
[TD="align: right"]2,000[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]15,000[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =IFERROR(INDEX(E$1:E1,AGGREGATE(14,6,(ROW(E$1:E1)-ROW(E$1)+1)/(A$1:A1=A2),1)),"Unknown") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


.. or if the formula is to be pre-populated further down the column, try

=IF(A2="","",IFERROR(INDEX(E$1:E1,AGGREGATE(14,6,(ROW(E$1:E1)-ROW(E$1)+1)/(A$1:A1=A2),1)),E2+D2-C2))[/QUOTE]

 

[Peter_SSs]

Thank you so much Peter. I am very impressed and I used the second option.

You're welcome.

A question: after the aggregate function you put 14,6; are these function numbers for Large and Product?

14 is for Large but 6 is to ignore errors.
The formula deliberately forces errors (division by 0) for all the rows above the formula where the Value in column A is not the one being looked for. So, once all those errors are generated, we take the largest row number where an error is not generated.
More information on the AGGREGATE function and its arguments here.


Actually, the alternative formula that I meant to post was this ..

=IF(A2="","",IFERROR(INDEX(E$1:E1,AGGREGATE(14,6,(ROW(E$1:E1)-ROW(E$1)+1)/(A$1:A1=A2),1)),"Unknown"))

.. but of course you can choose whatever suits you best.

 

[Philippevr]

Peter, I am really fascinated how Excel experts like you find these formulas. How do you get there. For me especially the part with Rows is still very abstract for me....