IF FUNCTIONS - HELP

[zgray]

I am using a drop down list with text descriptions. I want to set up a
system to say that "if" a certain selection is chosen then a date will follow
each being different:

For example:
drop down contains two selections 1. Payment Due 2. Payment due net 30 days.

I want to make the appropriate date calculate based on the option I choose
from the drop down. all ship dates are realtive not only to the drop down
choice, but the date the order was entered

 

[Frank Kabel]

Hi
maybe a lookup table combined with vLOOKUP will do. But you may explain in
more detail your different conditions and the expected result for each

 

[zgray]

Thank you. I will try to describe a little better what I am looking to
accomplish

I have one column that has an "Order Entered" column. the next column has a
drop down list of 6 options stating what their terms are, i.e. balance due
upon completion, net 30 days, 1/2 down net 30 days (all in text). I want to
be able to have the program calculate what calendar date their down payments
and/or final payments will be due.

 

[JulieD]

Hi

because your drop down contains a mixture of text / dates & numbers IMHO the
best way to approach this is to make another table which has the drop down
text for each option in the first column, in the second column, the deposit
% (enter as numbers only followed by % sign, leave blank if not relevant),
and in the third column the number of days (digits only) from order date the
balance is due.
Assume this table is on Sheet2!A1:C7 (with headings in row 1)
(e.g.
Option..............................Deposit%....NoDays
1/2 down net 30 days........50%............30
net 30 days............................................30

You could then construct a formula next to the drop down box in sheet1 along
the lines of
(assume order date in A2, total amount payable in B2 and drop down choices
in C2)

=IF(VLOOKUP(C2,Sheet2!A2:C7,2,0)<>"",TEXT(VLOOKUP(C2,Sheet2!A2:C7,2,0)*B2,"$#,###.00")
& " payable as deposit. ","")
&
IF(VLOOKUP(C2,Sheet2!A2:C7,2,0)<>"",TEXT(B2-VLOOKUP(C2,Sheet2!A2:C7,2,0)*B2,"$#,###.00
"),TEXT(B2,"$#,###.00 "))
& "payable on the " & TEXT(A2+VLOOKUP(C2,Sheet2!A2:C7,3,0), "dd-mmm-yyyy")

(all in one cell)

Hope this helps

Cheers
JulieD

 

[zgray]

Julie,

Thank you that was very insightful, however, also more info than neccesary.
Let's assume that the text/terms means nothing, but is only words. what I
want the formula to do is:

Let's assume the client has "50% downpayment, net 30 day terms" all I want
it to do is take the date in which the order was entered, and the ship date,
and place in one cell a two week date from the date of order for the
downpayment and a date thirty days past the ship date.

I want to be able to repeat this process for each different variable (6) in
the "Terms" drop down list.

If you can help me with this I will make sure to give you credit to my boss,
when I impress him! =)

Thanks,
Zach

 

[JulieD]

Hi

still use the same concept of a lookup table - as you need to specify in
numbers what you mean by the terms (in the following formulas i'm assuming
that the table is on sheet2!a1:C7)

Terms...........................Days to downpayment......Days to final
payment
1/2 down net 30 days........15............................................30
net 30
days........................................................................30

then in a cell on the original sheet for the downpayment date (D2)
(assume order date in A2, total amount payable in B2 and drop down choices
in C2, D2 date of downpayment, E2 date of final payment)

=IF(VLOOKUP(C2,Sheet2!A2:C7,2,0)<>"",VLOOKUP(C2,Sheet2!A2:C7,2,0)+A2,"")

for final payment date (E2)
=VLOOKUP(C2,Sheet2!A2:C7,3,0)+A2

Hope this helps

Cheers
JulieD

 

[zach]

Julie,

Thank you for your patience. I think we are almost there. I am going to
lay out my design, you might need to speak pretty common for me. Hopefully
the third time is a charm. I tried what I think you had, but getting #N/A.
Do you have an e-mail address that I could send the file to you. I think it
would describe what I am trying to accomplish a little better.

Thank you for all of your help.

Zach

 

[JulieD]

Hi Zach

email me at julied_ng at hcts dot net dot au

Cheers
JulieD