|
IF FUNCTIONS - HELP
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 |
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" wrote: 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 |
Hi Zach
email me at julied_ng at hcts dot net dot au Cheers JulieD "zach" wrote in message ... 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" wrote: 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 |
| All times are GMT +1. The time now is 10:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com