Best Way to Modify current IF() statement??????

[WebWizard97]

---------------------------------------------------------------

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.

---------------------------------------------------------------

 

[Allen]

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.

 

[ibeetb]

use a Select Case statement. I have done something very similar

 

[WebWizard97]

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.

 

[Allen]

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