Error message: "A value used in the formula is of the wrong data type".

markast

New Member
Joined
Mar 29, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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!o_O

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!!!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Forum statistics

Threads
1,175,494
Messages
5,897,736
Members
434,675
Latest member
AmitSatnalika

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top