Hello House, so i have this file - attached, for item code in column C, I have 999, when i enter the qty (Column E) as 1, and the opening balance (Column I) as 1, i should get 2 in Column J1 on the 1/jan/2019. Now on the 3rd Jan 2019, for the same item code of 999, i want the last Daily balance of 999 (1st Jan 2019) to appear in column I3. so any time I enter the same item code it should fetch the last daily closing balance for that item code. so there would be different closing balance on any date for a particular item code. PLEASE HELPPPPPPP!!!!
Hello,
The problem with your approach is that you need to tell Excel where to look and I can imagine that next time a laptop is populating the list in a new row can be the day after or 10 days after, so how to tell Excel where to look in the exact cell? You can probably do this but I can only imagine that such formula will be a complex one, or you need to use a macro.
I suggest another approach. See attached picture below.
First, create a separate Item list.
Second, create a separate Activity list.
With this setup you have a table with all your items listed and per item you will see the actual balance, as it is now.
The formula to get the balance per item is an array formula, if needed you can read more about array formulas by following the link below.
https://www.myonlinetraininghub.com/excel-array-formula
Hi Vic
See if this helps.
Note that I am unable to add the formula into cell I2 as you have an opening balance there.
I suggest you start your list with all items with 0 opening balance and input them (new opening balance) via the Quantity field (column E).
i.e Laptop in row 2 will be entered twice. Once to create the opening balance and the 2nd to add a new item.
This will then allow you to copy the formula in column I to cell I2.
Sunny
Hello sunny, thanks so much, your formula did work fine. I am much grateful man. However, i did try a formula of my own because i was working on it after i made the post. I did the matching of the item code with the date and it came out pretty well. This is the code: =IFERROR(INDEX($J$5:$J$33,MATCH(1,($C$5:$C$33=$C7)*($A$5:$A$33<$A7),0)),0)
For the user who recommended this https://www.myonlinetraininghub.com/excel-array-formula Thanks so much. it was helpful. my problem is solved now. Thanks for your response.
Hello Again, please there is one pressing issue which i have with the excel. attached is the excel file.
If on the 1st of Jan. 2019 I received 1 in qty column of RECEIPT sheet, and on 1st of Jan. 2019 I issued out 1 on the qty column of the ISSUED sheet, I should have a balance of 0 for 1st of January 2019 on the Balance column of the RECEIPT sheet. If i did not issue out anything on the ISSUED sheet for 1st of January 2019, i should have Balance of 1 in the Balance column of the RECEIPT sheet. SO my balances on the RECEIPT sheet should sync properly with the date of receipt and issued.
Thanks very much
