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.
----------------------------------------------------------
-----
.
|