Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Way to Modify current IF() statement??????
---------------------------------------------------------------
I have developed a spread sheet that displays the data / contents of one cell in another cell when the following conditions are true / have been met. Example: Cell A2 contains a Ship Date (i.e. 11-12-2003) Cell B2 contains a Sell Price (i.e. $400.00) In addition to the above columns (Ship Date & Sell Price) I have 12 columns labeled Jan 03 (C1 Header) - Dec 03 (N1 Header) for the months of the year. With the help of some group members I developed the following IF() statement that enters the sell price into the correct month of the sale when the following conditions have been met / are true Condition A: IF(MONTH(A2=Header Month(C1 thru N1))) Condition B: IF(YEAR(A2=Header Year(C1 thru N1))) =IF($A2<0,IF(MONTH($A2)=MONTH(C$1),IF(YEAR($A2)=Y EAR(C$1),$B2,""),""),"") this formula works great, however, I need to modify it a little bit, and would like some input as to which way is the best way (Additional Nested IF()s or VBA) and how to formulate / write it. Products that will ship on or after the 24th of the month need to be entered into the following months (I.E. Item Ships Sep 24 enter into Oct Sales, etc.). I need to add the following modifications to the above IF() statement: 1.) IF (the Ship Date DAY <= 23 Display Sell Price) 2.) IF (the Ship Date MONTH = Header Month - 1) and (Ship Date DAY =24 Display Sell Price) - this will catch all sales that appear at the end of each month 24 - last day of the month. Any assistance provided will be greatly appreciated, and thank you in advance to all that may offer assistance. --------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Way to Modify current IF() statement??????
Create a custom function:
Function Bucket(datShipDate As Date, datMonth As Date, varVal As Variant) As Variant If datShipDate = 0 Then Bucket = "": Exit Function If varVal = 0 Then Bucket = "": Exit Function If Year(datShipDate) = Year(datMonth) And Month (datShipDate) = Month(datMonth) And Day(datShipDate) <= 23 Then Bucket = varVal ElseIf Year(datShipDate) = Year(datMonth) And Month (datShipDate) = Month(datMonth) - 1 And Day(datShipDate) 23 Then Bucket = varVal ElseIf Year(datShipDate) = Year(datMonth) - 1 And Month (datShipDate) = 12 And Month(datMonth) = 1 And Day (datShipDate) 23 Then Bucket = varVal End If End Function Then, use this formula to call it: =Bucket($A2,C$1,$B2) where $A2 is shipDate, C$1 is the month, and $B2 is the amount. -----Original Message----- ---------------------------------------------------------- ----- I have developed a spread sheet that displays the data / contents of one cell in another cell when the following conditions are true / have been met. Example: Cell A2 contains a Ship Date (i.e. 11-12-2003) Cell B2 contains a Sell Price (i.e. $400.00) In addition to the above columns (Ship Date & Sell Price) I have 12 columns labeled Jan 03 (C1 Header) - Dec 03 (N1 Header) for the months of the year. With the help of some group members I developed the following IF() statement that enters the sell price into the correct month of the sale when the following conditions have been met / are true Condition A: IF(MONTH(A2=Header Month(C1 thru N1))) Condition B: IF(YEAR(A2=Header Year(C1 thru N1))) =IF($A2<0,IF(MONTH($A2)=MONTH(C$1),IF(YEAR($A2)= YEAR (C$1),$B2,""),""),"") this formula works great, however, I need to modify it a little bit, and would like some input as to which way is the best way (Additional Nested IF()s or VBA) and how to formulate / write it. Products that will ship on or after the 24th of the month need to be entered into the following months (I.E. Item Ships Sep 24 enter into Oct Sales, etc.). I need to add the following modifications to the above IF() statement: 1.) IF (the Ship Date DAY <= 23 Display Sell Price) 2.) IF (the Ship Date MONTH = Header Month - 1) and (Ship Date DAY =24 Display Sell Price) - this will catch all sales that appear at the end of each month 24 - last day of the month. Any assistance provided will be greatly appreciated, and thank you in advance to all that may offer assistance. ---------------------------------------------------------- ----- . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Way to Modify current IF() statement??????
use a Select Case statement. I have done something very similar
"Allen" wrote in message ... Create a custom function: Function Bucket(datShipDate As Date, datMonth As Date, varVal As Variant) As Variant If datShipDate = 0 Then Bucket = "": Exit Function If varVal = 0 Then Bucket = "": Exit Function If Year(datShipDate) = Year(datMonth) And Month (datShipDate) = Month(datMonth) And Day(datShipDate) <= 23 Then Bucket = varVal ElseIf Year(datShipDate) = Year(datMonth) And Month (datShipDate) = Month(datMonth) - 1 And Day(datShipDate) 23 Then Bucket = varVal ElseIf Year(datShipDate) = Year(datMonth) - 1 And Month (datShipDate) = 12 And Month(datMonth) = 1 And Day (datShipDate) 23 Then Bucket = varVal End If End Function Then, use this formula to call it: =Bucket($A2,C$1,$B2) where $A2 is shipDate, C$1 is the month, and $B2 is the amount. -----Original Message----- ---------------------------------------------------------- ----- I have developed a spread sheet that displays the data / contents of one cell in another cell when the following conditions are true / have been met. Example: Cell A2 contains a Ship Date (i.e. 11-12-2003) Cell B2 contains a Sell Price (i.e. $400.00) In addition to the above columns (Ship Date & Sell Price) I have 12 columns labeled Jan 03 (C1 Header) - Dec 03 (N1 Header) for the months of the year. With the help of some group members I developed the following IF() statement that enters the sell price into the correct month of the sale when the following conditions have been met / are true Condition A: IF(MONTH(A2=Header Month(C1 thru N1))) Condition B: IF(YEAR(A2=Header Year(C1 thru N1))) =IF($A2<0,IF(MONTH($A2)=MONTH(C$1),IF(YEAR($A2)= YEAR (C$1),$B2,""),""),"") this formula works great, however, I need to modify it a little bit, and would like some input as to which way is the best way (Additional Nested IF()s or VBA) and how to formulate / write it. Products that will ship on or after the 24th of the month need to be entered into the following months (I.E. Item Ships Sep 24 enter into Oct Sales, etc.). I need to add the following modifications to the above IF() statement: 1.) IF (the Ship Date DAY <= 23 Display Sell Price) 2.) IF (the Ship Date MONTH = Header Month - 1) and (Ship Date DAY =24 Display Sell Price) - this will catch all sales that appear at the end of each month 24 - last day of the month. Any assistance provided will be greatly appreciated, and thank you in advance to all that may offer assistance. ---------------------------------------------------------- ----- . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Way to Modify current IF() statement??????
Allen,
I tried the custom function you mentioned, however, I am getting a Compile Error: Else without If for line 1. Any suggestions???? Thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Way to Modify current IF() statement??????
When you pasted the below code, it included line breaks;
you need to remove line breaks as necessary...I believe you left a break on the first 2 lines...be sure the ": Exit Function" is on the same line as the "IF" statement. If datShipDate = 0 Then Bucket = "": Exit Function If varVal = 0 Then Bucket = "": Exit Function Rgds, Allen -----Original Message----- Allen, I tried the custom function you mentioned, however, I am getting a Compile Error: Else without If for line 1. Any suggestions???? Thanks in advance. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Current year and current week number | Excel Discussion (Misc queries) | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Modify a UDF please? | Excel Worksheet Functions | |||
Having the current time inserted w/o updating the current time | Excel Worksheet Functions | |||
Can I automatically enter the current date or current time into a | New Users to Excel |