coding arrays to change value if promo is selected

[PritiOne]

Hello all.

I have a complicated situation at hand and need help in producing a final product.

Situation:

I have a promo running and if a customer is eligible, he would get certain items on the product list at 50% off. However, I cannot change the actual prices on the product list b/s if the customer does not qualify for the promo he's pay list price.

These products are not grouped on the price list (I cannot easily group them into a named category). Unless you can help me with that as well.

On the input sheet I have a drop down box that Sales will use to choose this promo. Once he does, I need the magic to happen...

Any suggestions? If I can do this using code - it would be preferred. Otherwise I am open to formulas as well.

Thanks in advance for your help!!!

P

 

[Oaktree]

One suggestion would be to link your dropdown box to a cell, then have item cost = listprice*if(isnumber(match(linkedcell, promolist,0)),0.5,1)

 

[PritiOne]

Thanks for the insight - but the dropdown isn't a place to link to the cells - I don't think. It's a place to activate code (as I am imagining it...)

 

[PritiOne]

Quite possibly - I am not fully understanding the methodology that would have to be coded for this to work???

 

[PritiOne]

Help!!!

 

[Zack Barresse]

Hi,


How did you create the ComboBox? What type is it? How is it popuulated? Where does the data come from? What cell(s) are you wanting to use the value of it? Can you post an example of your worksheet?

 

[PritiOne]

It's a drop down validation list. List is comprised of 3 different promos we are going to run. I am starting with one and going to be building from there.

On a different spreadsheet I have a quote sheet that pulls infrom from a price sheet (another sheet in the workbook) The price per product line item is pulled from a Vlookup of the product number type in off the price sheet.

If program of 50% off certain items is selected initially, on the quote sheet the prices should reflect 50% off those products that are part of the program.

However - on the price sheet the products are spread out, (I can't figure out a way to connect them easily...) My initial formula, that works is:

=IF(PromoPricing?="(#96)",IF(ISNUMBER(MATCH(NewProdList,96List,0)),0.5,1)*IF(S19="",0,VLOOKUP($S19,ProductTable,TERMCODE,0)),IF(S19="",0,VLOOKUP($S19,ProductTable,TERMCODE,0)))

But I have to get tricky to add the logic to fit all the programs.

You'll notice that I did type in the list of promo products (96List) for this promo, that's b/c there's only about 10, for the other promo, there are way too many to actually type in and update as product numbers change.

Hope I am making sense here... Thanks!

 

[Zack Barresse]

We would need to see a posted example of your spreadsheet. Try posting some examples using the HTML Maker.

 

[PritiOne]

I can't figure it out.. How do I post the workbook?

I've downloaded the add in.

 

[Zack Barresse]

Read this: http://www.mrexcel.com/board2/viewtopic.php?t=92622

If that doesn't help, I don't know what will.