Hi everybpdy!
I am trying to create an "Inventory" sheet getting informed by a "Transactions" sheet. The quantity of the items outgoing from Inventory is to be calculated by a SUMIFS formula summing up the quantities a certain inventory item is being registered with on Transactions, upon condition that the transaction type is "export" and the transaction status is "delete (from inventory)". All fields have been checked as for the data type and this is correct. The formula seems to be working in the first two rows but thereafter the value error occurs. It seems that the formula gets affected by the fact that the Transactions sheet has only two rows, because once I expand the Transactions table beyond the first two rows, the value error gives its place to 0 outcome. It also seems that the calculation takes into consideration the relative position of the items on both sheets. If relative position is identical, the calculation takes place correctly, otherwise returns 0. Please note that I have tried the same formula in another workbook with nearly identical data-design and is working pretty well over there, the main difference being that, now in the second workbook, the Inventory product list is coming as the appended outcome of different category-product-queries, while in the first workbook where the formula is working correctly, the Inventory product list is simply data-transfer from the different category-product-tables without power-query having been involved. My intention with this second workbook, was to have the Inventory product list being automatically updated whenever any seperate category-product-table is being updated. Perhaps you have understood that I am not an Excel expert. Sorry I cannot upload the sheet for you to see: it is in Greek!
PS: Another difference (of no importnace, I think, but I mention for yout to have the whole thing): in the first workbook, the Transactions products are being chosen from a single, autonomous drop-down list, while in the second workbook the Transactions product drop-down list is dependent on the choice made out of a product-category list. --Many thanks for your help!!!
I am trying to create an "Inventory" sheet getting informed by a "Transactions" sheet. The quantity of the items outgoing from Inventory is to be calculated by a SUMIFS formula summing up the quantities a certain inventory item is being registered with on Transactions, upon condition that the transaction type is "export" and the transaction status is "delete (from inventory)". All fields have been checked as for the data type and this is correct. The formula seems to be working in the first two rows but thereafter the value error occurs. It seems that the formula gets affected by the fact that the Transactions sheet has only two rows, because once I expand the Transactions table beyond the first two rows, the value error gives its place to 0 outcome. It also seems that the calculation takes into consideration the relative position of the items on both sheets. If relative position is identical, the calculation takes place correctly, otherwise returns 0. Please note that I have tried the same formula in another workbook with nearly identical data-design and is working pretty well over there, the main difference being that, now in the second workbook, the Inventory product list is coming as the appended outcome of different category-product-queries, while in the first workbook where the formula is working correctly, the Inventory product list is simply data-transfer from the different category-product-tables without power-query having been involved. My intention with this second workbook, was to have the Inventory product list being automatically updated whenever any seperate category-product-table is being updated. Perhaps you have understood that I am not an Excel expert. Sorry I cannot upload the sheet for you to see: it is in Greek!
PS: Another difference (of no importnace, I think, but I mention for yout to have the whole thing): in the first workbook, the Transactions products are being chosen from a single, autonomous drop-down list, while in the second workbook the Transactions product drop-down list is dependent on the choice made out of a product-category list. --Many thanks for your help!!!
