1
=IF('Inventory Management'!C6="","",'Inventory Management'!O6-SUMIFS('Sales (Release/Transfer) Order'!$J$6:$J$65764,'Sales (Release/Transfer) Order'!$E$6:$E$65764,'Inventory Management'!C6))
C6 is lot number from warehouse (every item has a lot number) O6 is Initial inventory(amount) The purpose of the function is calculating 'stock on hand'
After set up everything in excel sheet, all functions work. However, if function doesn't work when I want to sort by warehouse or by date.
Please advise, thank you so much!
Rita Jhuang
Posted 2019-12-12T14:56:09.293
Reputation: 11
I see two tabs in your formula, which worksheet are you sorting? If you are sorting the "Inventory Management" tab, then that could move the cell reference from O6 to someplace else. You'll need to figure out how to keep the initial inventory at the top of your tab, or create a formula that finds the initial inventory amount even if the data is sorted (maybe a
sumifs). – gns100 – 2019-12-12T18:23:44.327The function inserted in 'inventory management,' and I'm sorting in the same tab. For now I removed all tab 'inventory management' in this function, it seems like it works, but I'm not sure if that's all what I should revise. – Rita Jhuang – 2019-12-12T19:31:49.310
"all if function don't work" means there is an error? or the result is wrong (not sorted)? or something else (any screenshot)? – p._phidot_ – 2019-12-12T19:53:11.620
Well, if function works perfectly before i sort. However after I sorted, it massed up. (the results didn't follow the row it should be) For now I remove the tab in the function if it is in the same tab, it works. Just need to make sure if I don't miss any other tips... – Rita Jhuang – 2019-12-12T20:11:52.510
you have
$-signs in your formula, this means that the data the formula reads will always be from the specified location; in certain situations this might be the source of the problem - I have hard to tell if it is for you. I'd say; show an example of the actual data and what you're attempting. – Hannu – 2019-12-12T21:07:04.103@RitaJhuang,, it's a natural phenomena, whenever records reshuffles especially between sheets or databases, creates impact on result (formula getting wrong references), if possible either share some sample data or link on cloud to test it !! – Rajesh S – 2019-12-13T03:56:34.470