System for offering special merchandise based on selection in drop-down box VBA?

Thank you for your aiding ChemistB, but the button for adding an attachment doesnt work properly.

Checked it in multiple browser now, Explorer and Chrome, both dont work.

Cant add attachment, please have a look if you can replicate same issue?

Added worksheet

You have understood correctly:
"I think at a discount, based on the choices made in the selection box above"


I have, in the real situation, like 8 productgroups with 6 products each.
Due to my lacking skills of Excel im not sure at all how to reach a solution. Appreciating your help; if you think its VBA then yes; please do move it.

I will, start to look into VBA and Excel so that when help arrives, i know what i have to do.

Again, thanks a lot for your time and effort Ali!

IF I'm understanding your objective correctly, I think you can do this formulaically. The following should be array entered (confirm with Ctrl + Shift + Enter instead of Enter):

In F14: =VLOOKUP(E14,Blad3!$F$4:$G$9,2,FALSE)-SUM(IF(COUNTIF(Blad3!$F$4:$F$8,$E$4:$E$9)>0,$F$4:$F$9))

Fill down

In F21: =VLOOKUP(E21,Blad3!$I$4:$J$9,2,FALSE)-SUM(IF(COUNTIF(Blad3!$I$4:$I$8,$E$4:$E$9)>0,$F$4:$F$9))

Fill down

Use a similar setup for your other groups. As you mentioned, conditional formatting can be used to make the desired row appear blank. Let me know if you need assistance with that.

Take a look at the attachment to see if it will suffice:

Very close!

(my bad
Yet; if i select A1 and A2 and so on, both in the upper part and in the lower part, it should return 0 on them.
A1 - A1 should be 0
A2 - A2 should be 0
B1 - B1 should be 0
B2 - B2 should be 0

And so on.

Very close!

Thanks CAntosh!

Alternatively, you could use (also array-entered):

F14: =INDEX(Blad2!$F$3:$F$18,MATCH(E14,All_Products,0))-SUM(IF(COUNTIF(A_Products,$E$4:$E$9)>0,$F$4:$F$9))

F21: =INDEX(Blad2!$F$3:$F$18,MATCH(E21,All_Products,0))-SUM(IF(COUNTIF(B_Products,$E$4:$E$9)>0,$F$4:$F$9))

This set up is a little easier to adjust for your different groups.

I'm not sure I understand. Do you mean if you select two products from the same group? What value should be shown then? Based on your expected values, it looks like the bottom part should show the added cost of swapping out one choice for the alternative. Could you please provide a new set of expected values for the scenario you're describing?

Keep in mind that the bottom values will appear blank if they're chosen, so the lower part should not matter much for selected products.

Take a look at the updated attachment and please try to give me a clearer idea of what I'm misunderstanding.

I will rework my quoting tool to match it like this, i will manage!

Thank your very much for helping me out!

Both to you and Ali, my appreciation will be shared through rating system.

Thanks again!

Glad to help, please let us know if you have any difficulty adapting your workbook.

Allright, i jumped the gun.

My expected outcomes arent what i was looking for.

I uploaded the file created by CAntosh, reworked the expected outcomes.

thanks in advance, again and once more :D

I see now. I didn't realize the lower boxes might only contain a few select entries. It looks like the formulae just need to be wrapped in an IF clause. Try the following (array-entered):

In F14: =IF(OR($E14="",COUNTIF($E$4:$E$9,$E14)>0),"",VLOOKUP(E14,Blad3!$F$4:$G$9,2,FALSE)-SUM(IF(COUNTIF(Blad3!$F$4:$F$8,$E$4:$E$9)>0,$F$4:$F$9)))

In F21: =IF(OR($E21="",COUNTIF($E$4:$E$9,$E21)>0),"",VLOOKUP(E21,Blad3!$I$4:$J$9,2,FALSE)-SUM(IF(COUNTIF(Blad3!$I$4:$I$8,$E$4:$E$9)>0,$F$4:$F$9)))

This is it!

Man, I envy people like you; making this up so easily!

Thanks a great lot! :D