View Single Post
  #4   Report Post  
JulieD
 
Posts: n/a
Default

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