Have cell allow a "Range" or multiple cells to be referenced

[itsnasian]

Hope this makes sense. Trying to get a problem solved to have a % discount & 'flat' $ discount apply to cell(s) referenced.

Product A, B & C are available in 3 different materials. Objective is to allow each material's Max Discount column to apply discount(s) referenced in cell J3 & K3, but only in the order of applying the percentage discount BEFORE the 'flat' $ discount. Formula in cell C3 works IF the cell "Range to apply discount" I3 = "B3".

Works just fine if I allow only one cell to be displayed in I3, but ideally, I'd want this to allow the said discount(s) in cell J3 & K3 to apply to any cells mentioned (for instance, for Product A Material 1, Product B Material 2). I had tried a drop-down menu using Data Validation, which in my actual project allows me to select ONE cell at a time (out of my list of options), which is okay, but it would be extra helpful to have a checkbox option to select which ones to apply the same discount to, instead of creating a massive embedded IF function to have each product in each material find whether it's cell is referenced in 20 different places (if I just copy the already functioning single cell reference tool).

The scale in which I'd like to use this in would be to apply specific discounts for one product, but a different discount for another product or material, and allow the input cell I3 to include a checkbox drop-down option (like the filter/sort), but not remove the data in the table (Range A2:G5 in this example), so I can hide any unnecessary columns and print, showing certain products & materials having a 10% discount, while others having a 20% discount, with an additional $5 off, etc. Any help/suggestions would be appreciated! https://onedrive.live.com/redir?res...uthkey=!ALWFylTJa4jUkSg&v=3&ithint=photo,.JPG

 

[itsnasian]

Sorry, I've tried to re-post or include images and can't seem to get it to work. I'm new to this.. Seems the last link works, and gives you a picture of what the whole problem is trying to solve - basically allowing me to apply multiple discounts to more than one cell that is referenced. Again, any help is appreciated!

 

[itsnasian]

I've now updated my formula for C3 to 'find' "B3" mentioned in either input cells I3 or I6. C3 =IF($I$3="B3",SUM((B3-(B3*$J$3))-$K$3),IF($I$6="B3",SUM((B3-(B3*$J$6))-$K$6),B3[FONT=arial, sans, sans-serif])) which works for my latest attempt (which will be copied and made relevant to other cells to calculate discounts applied), though[/FONT] it doesn't yet solve my ultimate goal of creating a single input cell (I3) that can 'mention' or refer a series of cells to apply the discount to, which will then be duplicated to allow two or more different types of discounts to be applied to specific cells in the sheet, without filtering (removing) those that don't apply, so it can be printed as one sheet that includes some products/materials at regular price, and some with discount combo 1, others with discount combo 2.