relate forms to formula

[jtswanny]

I need to give the users the option of two different payment terms, I want to use a drop box or something and then when they make a selection the formula is based on the selection. Anyone know how to start this, I started with forms, but don't know how to reference the selection...Am I going in the right direction....Example: if net 60 selectec ce A1*50% if COD selected A1*60%

 

[maxflia10]

=IF(B1="ce",a1*50,a1*60) should get you started

 

[jtswanny]

Thanks for the help. but that was cell not 'ce' I need to reference the selection made by using the drop box, any other ideas...

 

[Aladin Akyurek]

On 2002-09-24 11:22, jtswanny wrote:
I need to give the users the option of two different payment terms, I want to use a drop box or something and then when they make a selection the formula is based on the selection. Anyone know how to start this, I started with forms, but don't know how to reference the selection...Am I going in the right direction....Example: if net 60 selectec ce A1*50% if COD selected A1*60%

What is in A1? And which cell is the "drop box"?

 

[maxflia10]

Substitute the "ce" for whatever is in the drop down box.

As Aladin asked what's in A1 and what cell is the drop down box located.

In my formula, I assumed B1 is the drop down box and the formula enter anywhere except A1 or B1

Using COD as a selection

=IF(B1="COD",A1*60,A1*50)

where B1 contains COD in the drop down
This message was edited by maxflia10 on 2002-09-24 13:05

 

[jtswanny]

That is what I am not sure about...the drop box. How do you create one and attatch it to the cell. B1=COD or B1=Net 60, how do I create a drop box that would only allow these two selections in cell B1..Hope this makes sense-Thanks, Jon

 

[Aladin Akyurek]

On 2002-09-24 14:17, jtswanny wrote:
That is what I am not sure about...the drop box. How do you create one and attatch it to the cell. B1=COD or B1=Net 60, how do I create a drop box that would only allow these two selections in cell B1..Hope this makes sense-Thanks, Jon

(1.) Activate B1.
(2.) Activate Data|Validation.
(3.) Choose List for Allow.
(4.) Type in the Source box: Net 60,COD
(5.) Click OK.

Then use:

=A1*VLOOKUP(B1,{"Net 60",50;"COD",60},2,0)

 

[maxflia10]

Jon,

I apologize. I thought you had the drop down figured out. Use Aladin's method instead of the IF function if the choices are limited to those two. Even if you have more, add them between the brackets.

 

[jtswanny]

I got the drop down, Thanks...Now I need to add vlookup and if than statements in the same formula. Here is what I got so far, but it is giving me an error....any suggestions.
=E17*VLOOKUP(n3,{"Net 60",IF($M$2952<100,E17*100%,IF($M$2952<500,E17*90%,IF($M$2952<1000,E17*85%,IF($M$2952>=1000,E17*82.5%))))"COD/Prepay",if($M$2952<100,E17*100%,IF($M$2952<500,E17*90%,IF($M$2952<1000,E17*85%,IF($M$2952>=1000,E17*82.5%)))},2,0)

what does the 2 and 0 at the end do.

 

[maxflia10]

Jon,

The 2 is the column number from your table from which your answer is returned.

The 0 means "false" meaning VLOOKUP is looking for an "exact" match, if it does not it returns a "#N/A".

What error message are you getting?

Is N3, the cell you're looking up or M2952?