Excel, Formulas and Functions

[YOLY00T]

I am making an inventory with three sheets
Inventory Inputs and Outputs everything works fine.
but when I try to add one more invoice and quantity in the input sheet it does not validate the quantity in the inventory sheet.
Please help me. I have already 1 week trying to figure out what is wrong and I can't find it.

Thanks

[#2]

Hi @YOLY00T,

Use a dynamic named range for the validation formula. This will ensure that the validation formula is always applied to the correct cells, even if you add new invoices and quantities to the input sheet.

To create a dynamic named range for the validation formula, follow these steps:

1. Select the entire column of cells where the quantities are entered in the input sheet.
2. Go to the Formulas tab and click Define Name.
3. In the Define Name dialog box, enter a name for the range, such as InputQuantities.
4. In the Refers to box, enter the following formula:

=OFFSET(A1,0,0,COUNTROWS(A:A)-1)​

1. Click OK.

Now, you can use the dynamic named range in the validation formula. For example, the following validation formula will compare the quantity in the input sheet to the quantity in the inventory sheet:

=InputQuantities <= InventoryQuantities

Replace InventoryQuantities with the cell reference for the quantity in the inventory sheet.

Once you have applied the validation formula to the input sheet, adding a new invoice and quantity should no longer cause the validation to fail.

Dynamic named ranges in Excel: https://www.ablebits.com/office-addins-blog/excel-dynamic-named-range/

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)