Need drop down list determined by qty input

**Mile029** · 08-16-2014, 04:11 PM

If I have a cell I input quantity in, I want my drop-down list in another cell to be a certain range. Can I do this.

I have attached a worksheet.

If I put in a min order of 200 then price can be $4.99 or $4.49
If I put in a min order of just 50, price can only be $4.99

If I put in a order of 6000 Price can be $2.99 all the way up to $4.99

Basically I want the Min Price cell to be locked down to a drop down to whatever quantity is ordered or up, so they can use a price point that is up to that point..

I think looking attached spreadsheet would help.

Also commission should be calculated at the price level.

**oeldere** · 08-16-2014, 04:27 PM

b16=

Please Login or Register  to view this content.

b17=

Please Login or Register  to view this content.

**Mile029** · 08-16-2014, 04:46 PM

Thank Oeldere, but I want my sales team to sell at a higher price, so this gives them the commission for that tier, So for example if they sell 11,000 units the price can be 2.49 all the way up to 4.99, but not 1.99. So basically it would be drop down of that range, 4.99 - 2.49. and the commission is based on the price sold at, not the amount sold, which I can change the vlookup to look at price rather than units sold.

**oeldere** · 08-16-2014, 05:08 PM

c16=

Please Login or Register  to view this content.

b17= inputcell with datavalidation =>

excel 2007 data => datavalidation
=AND(B17>=D16,B17<=C16)

b18 =

Please Login or Register  to view this content.

**mehmetcik** · 08-16-2014, 05:38 PM

Try This:

I used the match function

**Mile029** · 08-19-2014, 06:12 PM

Thank you guys, I got it figured out. I'm going to use a simple Vlookup and work with a list, this will prevent them from using a lower sales price for a low order quantity. See attached. Only problem I'm having is that if they put in a lower quantity and a lower price was in the min price from before, it keeps that price, anyway to prevent that?

**oeldere** · 08-20-2014, 02:30 PM

Did you tried my solution in #4?

Need drop down list determined by qty input

LinkBack

Thread Tools

Rate This Thread

Display

Need drop down list determined by qty input

Re: Need drop down list determined by qty input

Re: Need drop down list determined by qty input

Re: Need drop down list determined by qty input

Re: Need drop down list determined by qty input

Re: Need drop down list determined by qty input

Re: Need drop down list determined by qty input

Thread Information

Users Browsing this Thread

Similar Threads

Macro Help with Input Box or Drop Down List

Auto populating field determined by previously input details

Drop down list based on input of two other drop down list

Pre-determined values in drop-down list

input range in drop down list

Bookmarks

Bookmarks

Posting Permissions