hi, i just wanted to let you know, that i've been using your file for 2 of our suppliers, and it really works!! my manager is really impressed with how i keep track of the cost and stock. thanks again!
@excelhighway3549
Жыл бұрын
Thanks for sharing!
@LSCrossing
11 ай бұрын
Thank you, this helped so much! Now I can keep track of my cost accurately! If anyone wants to use it for Google sheets - you have to modify the Index of Batch formula. =IF(A2="", "", SMALL(FILTER(ROW(Purchases!C:C) - MIN(ROW(Purchases!C:C)) + 1, Purchases!C:C = C2), I2)).
@SmallFamilyDiaries
11 ай бұрын
Hi, I've got doubt with your new formula. It's shown that function isn't valid with Filter. Can you help Me?
@excelhighway3549
11 ай бұрын
Glad it was helpful!
@LSCrossing
11 ай бұрын
@@SmallFamilyDiaries the guy that helped me also added another formula in Cell O2: =FILTER(ROW(Purchases!C:C) - MIN(ROW(Purchases!C:C)) + 1, Purchases!C:C = C2). I'm not an expert to understand how it works, but it works for me. Reminder, these modify formulas are only for Google Sheets as the video produced for Excel.
@SmallFamilyDiaries
11 ай бұрын
@@LSCrossing Thank you so much.
@excelhighway3549
2 жыл бұрын
The file is available on my Patreon page: www.patreon.com/ExcelHighway
@vincentlihaka3236
Жыл бұрын
the tutorial is very good, how do i acces the file
@excelhighway3549
Жыл бұрын
@@vincentlihaka3236 thank you, you can find the file on my Patreon page
@amritrathod3941
Жыл бұрын
Yes it's very good file
@danielleredmanalang3501
Жыл бұрын
hello can I request an access for this file please. Thank you.
@excelhighway3549
Жыл бұрын
@@danielleredmanalang3501 The file is available on my Patreon page: www.patreon.com/ExcelHighway
@myzidan
Ай бұрын
I'm really appreciating your generosity on viewing the formulas although it is originally paid! I replicated the spreadsheet after much effort (which clearly indicates your original effort on creating it). But I added a little enhancement, instead of using the SMALL function I filtered the used up batches out of all batches, so that the only remaining batches are the one that still has things on it. Here it is after I did that: G3 =IF(A3 = "", "", MIN(IF((buy!C:C = B3) * ISERROR(MATCH(buy!A:A, FILTER(G$2:G2, I$2:I2 = 0), 0)), ROW(buy!C:C) - 1, ""))) A3 is sell identifier, buy!C:C is the product name, buy!A:A is the batch identifier, G$2:G2 is the indices of batches for the previous row, I$2:I2 is the quantity remaining in the batch Thank you, this sheet would ultimately help me!
@excelhighway3549
Ай бұрын
Thank you for your feedback. I'm really glad you found value here and happy it helped you.
@rameshgttc
Ай бұрын
hi, if possible can you show the video
@rameshgttc
Ай бұрын
Hi, quite interesting logic, it gives an error when sale quantity is larger than purchase qty. can we set shortage qty? Secondly can we avoid repeating of stock out batch material search for every new order?
@excelhighway3549
Ай бұрын
Hi, I'm sure you can add those adjustments. I built this a while ago and improvements can definity be added.
@bonmonil
Жыл бұрын
Wow, I've done many fifo calculations before, I like your method of simplifying and using helper columns. it works best on huge datasets. Is it possible to upload the file or send it?
@excelhighway3549
Жыл бұрын
Thank you. The file is available 9n my Patreon page or Etsy.
@naondadoleo4082
Жыл бұрын
Your method is really impressive! Would it be possible to calculate the FIFO price for an assembled item "XX01" wich is composed by three other part numbers?
@excelhighway3549
Жыл бұрын
Perhaps by creating this for our assembled item and link it to the components using lookup.
@naondadoleo4082
7 ай бұрын
@@excelhighway3549 I've achieved it, but in the end the spreedsheet became too heavy and it takes more than 2 hours to calculate, my company results everymonth. =/
@rok2489
Жыл бұрын
Thank you Elad this really helped alot will save me alot of time in the future, and thanks for the small correction as well.
@excelhighway3549
Жыл бұрын
You are very welcome
@DaBruno-l2w
9 ай бұрын
I followed exactly the same formulas provided as well as the template, but my spreadsheet doesnt seem to provide any information. My batch# says BATCH# and Price/Unit says Price/Unit. Most of the other cells are empty. Please assist if you can.
@excelhighway3549
9 ай бұрын
I just replied to your email, I hope that will help.
@gigie0970
Ай бұрын
I am facing this same issue. How do i rectify please..
@ШоистаНазарова-ю9ъ
9 ай бұрын
Hello, how can i get this your excel spreadsheet?
@excelhighway3549
9 ай бұрын
Hi, the file is available on my Patreon page: www.patreon.com/ExcelHighway
@יוחנןהוכהייזר
Жыл бұрын
looks so good, i saw a few videos offering cogs-fifo solutions, this definitely looks the best. how can i get the file? it would really help me! thanx
@excelhighway3549
Жыл бұрын
Thank you, please send me an email
@lunardisetiawan2059
3 ай бұрын
For A lot of data >10000 orders, it took a while to calculate, any solution to this?
@excelhighway3549
3 ай бұрын
Thanks for your feedback, I'll look into this.
@tolusdiary9437
Жыл бұрын
This is awesome. How can I get the file please. Can I use this for food costs?
@excelhighway3549
Жыл бұрын
Thank you, the file is available on my Patreon page: www.patreon.com/ExcelHighway You can use it for any cost
5 ай бұрын
Hello, What is the purpose of "Last Order" value in P1?
@excelhighway3549
5 ай бұрын
It's used as a boundary. After that number , the rows will be blank.
@gigie0970
Ай бұрын
That means we can change the value depends on the last order number?
@PaoloDeLuca-g6s
2 ай бұрын
Nice, one question, if u have a batch depleted, how can i start only the active one for the same product? for expample i dont' want to have row 9, 10, 11 and it start with the row 12?
@excelhighway3549
Ай бұрын
You can deplete the quantity of the purchased amount in the purchases sheet.
@Dufresne66
Жыл бұрын
Hi I Also have problem with the Index of Batch result. My Batch numbers are unique (A1, A2, A3 stc...). If I click on the Fx button to check the equation it shows me the rigth value, however in the cell is always shows me 0... it's a bit confusing. Could you help with with that?
@excelhighway3549
Жыл бұрын
Sure , please send me your file by email
@Dufresne66
Жыл бұрын
@@excelhighway3549 Thx I've send you by mail.
@Dufresne66
Жыл бұрын
@@excelhighway3549 Thx now it works. The solution is in column F is a dynamic array formula so after typing in the formula you should use Ctrl+Shift+Enter insted of simple pushing Enter
@matteoscuderi7274
Жыл бұрын
Thanks a lot! And one useful formula never seen before :) i'm trying to put it in LIFO mode (descending) but seems that if i purchase the same item AFTER a selling, the balance sheet change the price of the purchased item (that i bought after the sales); example 01.01.2022 BUY 5.000 of A1 at 7 02.01.2022 BUY 3.000 of A1 at 10 03.01.2022 SELL 4.000 of A1 (sheet takes 3.000 x 10 and then 1.000 x 7 = 37.000 correct) 04.01.2022 BUY 1.000 of A1 at 12 (sheet update the balance to 1.000 x 12 + 3.000 x 10 =42.000 instead of 37.000; but at 3 january i dont have any of these 1.000 x 12), also sales sheet change from 37k to 42k in the 3th january row
@excelhighway3549
Жыл бұрын
Sorry, I don't understand. Canyou send me an email with more details?
@thaissrodrigues97
Жыл бұрын
Thank you so much for your video - how can I access the file?
@excelhighway3549
Жыл бұрын
You are welcome. The file is available on my Patreon page: www.patreon.com/ExcelHighway
@GianJommelEligio
Жыл бұрын
what is the formula of "last order"? in sheet balance, column P row 1
@excelhighway3549
11 ай бұрын
=MAX(Sales!A:A)
@GianJommelEligio
11 ай бұрын
@@excelhighway3549 a great help with your template. pls continue what you are doing
@mhpgasservice8984
Жыл бұрын
THIS IS I WAS LOOKING FOR AND IS WONDERFUL. PLZ SEND THE FILE
@excelhighway3549
Жыл бұрын
Thanks, the file is available on my Patreon page
@FICAManager
Жыл бұрын
hello, if you don't mind can i get the excel?
@excelhighway3549
Жыл бұрын
Sure, please send me an email
@monikas3265
Жыл бұрын
Hi I would like to get the access to the file. Thanks in advance!
@excelhighway3549
Жыл бұрын
The file is available on my Patreon page: www.patreon.com/ExcelHighway
@monikas3265
Жыл бұрын
@@excelhighway3549 thanks!
@danielleredmanalang3501
Жыл бұрын
Is there other formula that is not being shown in the video? since I already watched it for how many times and still mine doesn't work.
@excelhighway3549
Жыл бұрын
All the steps are in the video. What isn't working for you?
@danielleredmanalang3501
Жыл бұрын
@@excelhighway3549 the index batch column it shows #num error
@Juniorsplanet
7 ай бұрын
Please share the link to download the template.
@excelhighway3549
7 ай бұрын
Please send me an email
@linardtuvera8138
Жыл бұрын
I am not getting the results for the formula on the index of the batch.
@excelhighway3549
Жыл бұрын
Are you using a unique batch number?
@amazingshorts5963
Жыл бұрын
ME TOO FACING WITH THIS PROBLUM
@excelhighway3549
Жыл бұрын
@@amazingshorts5963 it's probably because you are not using a unique batch number. Please check and advise
@hbcreation7903
Жыл бұрын
HI CAN I TAKE THE FILE
@excelhighway3549
Жыл бұрын
Sure, the link is in the description
@AmritRathod-t4e
Жыл бұрын
its nice file I want to try this
@excelhighway3549
Жыл бұрын
Thanks
@mmyfc7850
11 ай бұрын
Thank you so much! :)
@excelhighway3549
11 ай бұрын
You're welcome!
@landk8625
2 жыл бұрын
It is interesting, I would like to have the file. Thank you very much
@excelhighway3549
2 жыл бұрын
Please send me an email
@Juniorsplanet
7 ай бұрын
Please share link to download
@excelhighway3549
7 ай бұрын
Please send me an email
@piotrchrzaszcz8685
Жыл бұрын
Hi - may I have the file ? :)
@excelhighway3549
Жыл бұрын
Yes, it is available on my Patreon page: www.patreon.com/ExcelHighway
@lebronjames2864
Жыл бұрын
Thanks a lot! I would really like the file
@excelhighway3549
Жыл бұрын
Sure, please send me an email
@enaldie5314
Жыл бұрын
i want this file please
@excelhighway3549
Жыл бұрын
The file is available on my Patreon page: www.patreon.com/ExcelHighway
@taijiharris5209
Жыл бұрын
Great video. Thank you. I would like to receive the file, please.
@excelhighway3549
Жыл бұрын
This file is available on my Patreon page. www.patreon.com/ExcelHighway
@mohammedelshimy7698
Жыл бұрын
Thanks
@excelhighway3549
Жыл бұрын
You are very welcome
@xXblueINsuitXx
Жыл бұрын
Hi this Viedo was really helpfull! Can you send me the excel file?
@excelhighway3549
Жыл бұрын
Thanks, you can find the file here www.patreon.com/ExcelHighway
@VivekVaish-f2n
Жыл бұрын
Thanks for this amazing video. Can you send me this file.
@excelhighway3549
Жыл бұрын
Thanks, the file is available on my Patreon page
@fakehippie2317
Жыл бұрын
thanks
@excelhighway3549
Жыл бұрын
You're welcome!
@den-fn2wh
Жыл бұрын
Great tool, link pls ;)
@excelhighway3549
Жыл бұрын
Thanks, please send me an email
@meliketas6425
Жыл бұрын
Hello, thank you for this video, it is very helpful to me. I will grateful if you could send me the file, thank you.
@excelhighway3549
Жыл бұрын
Thank you, the file is available on my Patreon page: www.patreon.com/ExcelHighway
@ronville9131
6 ай бұрын
Send me File please, this is great i need this.
@excelhighway3549
6 ай бұрын
The file is available on my Patreon page: www.patreon.com/ExcelHighway
@OranJohnson
Жыл бұрын
Please send the file.
@excelhighway3549
Жыл бұрын
The file is available on my Patreon page: www.patreon.com/ExcelHighway
@AmritRathod-t4e
Жыл бұрын
Please Share this File
@excelhighway3549
Жыл бұрын
The file is available on my Patreon page: www.patreon.com/ExcelHighway
Пікірлер: 104