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

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

--
Regards
Frank Kabel
Frankfurt, Germany
"zgray" schrieb im Newsbeitrag
...
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

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.






"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany
"zgray" schrieb im Newsbeitrag
...
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

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%....No Days
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" wrote in message
...
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.






"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany
"zgray" schrieb im Newsbeitrag
...
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

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" wrote:

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%....No Days
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" wrote in message
...
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.






"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany
"zgray" schrieb im Newsbeitrag
...
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

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,Sh eet2!A2:C7,2,0)+A2,"")

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

Hope this helps

Cheers
JulieD

"zgray" wrote in message
...
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" wrote:

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%....No Days
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" wrote in message
...
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.






"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany
"zgray" schrieb im Newsbeitrag
...
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