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

Philippevr

New Member
Joined
Jul 14, 2014
Messages
6
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
Code:
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

New Member
Joined
Jul 14, 2014
Messages
6
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

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows

Philippevr

New Member
Joined
Jul 14, 2014
Messages
6

ADVERTISEMENT

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
DescriptionOpening Stock LevelReceptionWithdrawalStock Final Level (end of the day)
Sugar1,500 05001,000
Corn5500014000069,000
Corn69,00008,00061,000
Sugar1,00020,0005,00016,000
Salt30,0000030,000
Salt30,00002,50027,500
Corn61,000014,00047,000
Sugar16,0002,0003,00015,000

This column B must have an automatic fill based on the previous final stock level hence I used the Lookup formula














































































<tbody>
</tbody>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,287
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

See if this is any use.

Excel Workbook
ABCDE
1DescriptionOpening Stock LevelReceptionWithdrawalStock Final
2SugarUnknown05001,000
3CornUnknown14000069,000
4Corn6900008,00061,000
5Sugar100020,0005,00016,000
6SaltUnknown0030,000
7Salt3000002,50027,500
8Corn61000014,00047,000
9Sugar160002,0003,00015,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))
 
Last edited:

Philippevr

New Member
Joined
Jul 14, 2014
Messages
6
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

ABCDE
1DescriptionOpening Stock LevelReceptionWithdrawalStock Final
2SugarUnknown05001,000
3CornUnknown14000069,000
4Corn6900008,00061,000
5Sugar100020,0005,00016,000
6SaltUnknown0030,000
7Salt3000002,50027,500
8Corn61000014,00047,000
9Sugar160002,0003,00015,000

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,287
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Philippevr

New Member
Joined
Jul 14, 2014
Messages
6
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....
 

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,146
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top