How to Create an Excel Inventory Formula with Unit and Case Drop-Down Lists
Question details
The user wants to set up an inventory worksheet with a drop-down list to select between 'each' and 'case', dynamically calculate the unit price, and multiply it by the quantity.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building an inventory management spreadsheet with dynamic pricing based on unit types (each vs. case).
- Observed behavior
- Needs formulas to correctly calculate the unit price depending on the drop-down selection and then compute the total inventory value.
Ensure your inventory data is organized in columns: Product Name, Units per Case, Case Price, Unit Selection (drop-down), Quantity, Calculated Price, and Total Value.
Use Data Validation and the IFS Function for Dynamic Pricing
Create a drop-down list using Data Validation and calculate the price automatically using the IFS function to handle 'case' and 'each' conditions.
This method relies on maintaining consistent data layout. By setting up a drop-down list, you eliminate data entry errors, and the IFS function smoothly toggles the math depending on the unit type selected.
Select the cell for unit selection (e.g., F2). Go to the Data tab, click Data Validation, choose 'List' under the Allow settings, and enter 'each,case' in the Source field.
Assuming E2 is the case price, C2 is units per case, and F2 is the drop-down, enter the formula '=IFS(F2="case",C2*E2, F2="each", E2/C2)' into your Calculated Price column (e.g., G2).
To find the total value based on the entered quantity in H2, enter the formula '=IF(H2="","Please enter a value in the quantity field.", G2*H2)' in the total value column.
Use VLOOKUP with a Master Product Table
If you have a large inventory, storing unit prices and pack sizes in a separate table and pulling them into your formula using VLOOKUP is more scalable.
Build Your Inventory Trackers in WPS Office
WPS Spreadsheet fully supports data validation, IFS, and VLOOKUP functions, making it incredibly easy to set up dynamic inventory trackers and perform complex unit calculations effortlessly.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your inventory document or create a new blank workbook.
- 2. Apply Data Validation: Highlight the target cells and navigate to the Data tab. Select Data Validation to create your 'case,each' drop-down list.
- 3. Enter Calculations: Type the =IFS(...) and =IF(...) formulas in your pricing and total columns to instantly calculate the dynamic costs.
- 4. Fill Formulas: Drag the fill handle down to copy the dynamic formulas across your entire inventory database.
Frequently Asked Questions
Why is my IFS formula returning a #NAME? error?
This happens if you are using an older version of Excel (like Excel 2016 or earlier) that does not support the IFS function. You can achieve the same result by nesting multiple IF functions, such as: =IF(F2="case", C2*E2, IF(F2="each", E2/C2, "")).
How do I create a drop-down list for 'each' and 'case'?
Select the cell where you want the drop-down. Go to the Data tab on the ribbon, choose Data Validation, change the 'Allow' setting to 'List', and type 'each,case' in the Source text box. Click OK.
Can I hide the #N/A error if the drop-down is left blank?
Yes. You can wrap your entire formula in the IFERROR function. For example: =IFERROR(IFS(F2="case",C2*E2,F2="each",E2/C2), "Please select a unit"). This will display a friendly message instead of an error code.
