Save discount value

[imran91]

Hi guys.

I want to create an Invoice file. Everything are under control but when it comes to discount, I found some problems. What I want to do is to create a system where staff can key in the discount amount in a cell and it will appear in the invoice. But here is the problems, the discount amounts are vary from each descriptions and quantity. So i make a data validation for staff to pick which item need to put the discount and i use the vlookup to make the discount value appear in the invoice. But when i change to another item in data validation, the discount amount in the previous item also changed.

For example: I choose number 1 in data validation and i key in 5%, so 5% will appear in number 1 section in the invoice. But when i choose another number, let say number 2, the 5% will go to the number 2 section in the invoice and discount value in number 1 will disappear because i use vlookup.

So, my question are, how to make the discount amount stays or saved in the invoice when i change the data validation to another number? I already attach a simple dummy file. hope you guys can help me..

Thanks a lot
Imran.

[vlady]

why not just put a validation lists of discounts.

[imran91]

Hi vlady

Thanks for your idea but I still can't figure out your idea because the way i understand, it is just the same with what i'm doing, just changing the data validation for number to discount. Still, it can't save the discount at the invoice. What i try to figure out is to make a formula in vlookup to make the value stays when data changed.

[vlady]

here is a sample of what i'm trying for an option.

DISCOUNT (2).xlsx

[imran91]

Thanks for the option vlady. Its a great idea. But the problems is the discount in your idea is fixed for every number because number 1 could be 40% or etc. I try to make improvement to your idea but i just found dead end.

[vlady]

so you have certain computation before you know what will be the discount?
something like.. if quantity is greater than 5 then discount is 5% otherwise 40% ?
it doesn't really rely on the number "1". because number 1 holds the range of discounts

e.g

if you choose Number 1 - range of quantity is 0 to 10 then discount is 5%

[imran91]

"Number" is just an indicator which items need to put discount because it just dummy file, in the real life it is different. I make data validation to the numbers, so that when i key in discount, the discount will appear in the invoice depending on the chosen number. Its like setting area for the invoice. I want to make it user friendly by making the data validation to the number. Its not like it have certain computation like what you are saying. It just a setting. But the problems is, how i want to make the discount stays in the invoice when i change the number in data validation?

[vlady]

then i think this is for vba...wait for someone to get interested in giving a code for this.
regards,
vladimir

[imran91]

Hi vlady. Yeah, that is what i'm thinking, using vba. Anyway, thank you so much for your ideas and discussion.