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

itsnasian

New Member
Joined
Mar 14, 2014
Messages
4
Hope this makes sense. :confused: Trying to get a problem solved to have a % discount & 'flat' $ discount apply to cell(s) referenced.
1kse1QA
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".
1kseGSe
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).
1ksf56Z
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

itsnasian

New Member
Joined
Mar 14, 2014
Messages
4
Sorry, I've tried to re-post or include images and can't seem to get it to work. I'm new to this.. :confused: 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

New Member
Joined
Mar 14, 2014
Messages
4
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,579
Messages
5,637,209
Members
416,961
Latest member
sigrid6940

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top