Excel, Formulas and Functions

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/