# relate forms to formula

#### jtswanny

##### New Member
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%

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
=IF(B1="ce",a1*50,a1*60) should get you started

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...

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"?

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

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

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)

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.

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.

Jon,

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?

Replies
5
Views
136
Replies
0
Views
187
Replies
1
Views
195
Replies
14
Views
432
Replies
4
Views
320

1,203,501
Messages
6,055,765
Members
444,821
Latest member
Shameer

### 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.

### Which adblocker are you using?

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

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