I’m working on a sheet that shows various discounts for products based on how much volume of product is purchased. I have a data validation box that lists the various amounts required (1 case, 2 case, etc.), and I have a sheet that has all the data (A1 is empty – column A is the case quantity – column B has a product name in B1 and then each discount based on the case quantity in Column A). I want the discount amount in Sheet 2 to fill in automatically based on the information on Sheet 1.
To meet this requirement we will create the drop down list to product and product code and then we use “Index” and “Match” function to return the amount according to product and product code.
Create the drop down list for product:-
- Select the cell C15.
- Go to Data tab > Data Validation > Setting > List > Source (B4:L4).
- Select the cell D15.
- Go to Data tab > Data Validation > Setting > List > Source (A5:A10).
- Now Enter the formula in Cell E15.
- Press Enter.
Whenever you will change the product and product code from the drop down list amount will get changed automatically.