conditional lookup macro looping

hi,

i have attached my sheet to this post... the macro code is in worksheet2...

in cell S8 of sheet2 im trying to do a calculation

the calculation for this particular scenario where we have four date after the external feed has updated my sheet is in cell S8
the logic for the calculation is :

check date in column E of sheet2 with the dates in column J of sheet1, for each date in column J of sheet1 that is on or before the date in column E of sheet2 do the following calculation

(1 + ((value in sheet1 column I associated with date in column J - value in sheet2 R8) * (days in sheet1 column K associated with date in column J / 360))

depending on how many dates in column J of sheet1 that are on or before the date in column E of sheet2, this calculation above would be done multiple times

next... (1 + (value in sheet2 column P that is associated with the date in sheet2 column E - value in sheet2 R8) * (days in sheet2 column M associated with date in sheet2 column E/ 360))

multiply the result from the expression highlighted in green with each result from each of the yellow expression, lets call this the denominator result...

so if four dates in column J of sheet1 are on or before the date in column E of sheet2 the "denominator result" would be

blue1* blue2* blue3* blue4*green

finally, the value in sheet2 column G that is associated with the with date in sheet2 column E / denominator result

the expression highlighted in red the the final value....for this particular date in sheet1 column E...

if the external feed populated only one date, thus column E had 1 date, this would be the final answer in cell S8... in the particular scenario in sheet2 there are four dates, hence the calculation is done four times and each result from the red expression is added, and that's the answer shown in cell S8

im not sure how to get started on this because the number of dates in column E is dynamic (i.e. depends on the update from external feed) and im not sure how the macro would be written that will use the correct dates and the associated values in sheet1 based on the date in sheet2 column E


please let me know if this makes sense..... the formula which i entered manually in cell S8 might also help in understanding what im doing in cell S8 of sheet2

if you could help me with this it would be awesome
thanks

im not quite sure i understand ur code...

sorry for the confusion....

forget about the logic in the previous post including color codes..

i think below is a better explanation


to further clarify .. for each date listed in column E of sheet2

there is a blue expression, a green expression and a red expression

if there are two dates listed in col E of sheet2 then the final answer will be

(red1/(blue1* green1)) + (red2/(blue2* green2))

(case 1)
...let say in column E of sheet2 the external feed only populated 1 date, lets say this date was 8/31/2011


notice that there are two dates listed in sheet1 column J that are on or before the date in column E of sheet2.

3/16/2011 (listed in I2 of sheet2)
and
6/15/2011 (listed in I3 of sheet2)
are on or before 8/31/2011

((1 + ((sheet1 I2 - sheet2 $R$8) * (sheet1 K2 / 360))) * (1 + ((sheet1 I3 - sheet2 $R$8) * (sheet1 K3 / 360))))

the above is basically the format for the blue expression
the blue expression above is for the specific case when only two dates in sheet1 column J are on or before the date in column E of sheet2, the date in column E of sheet2 i am referring to is 8/31/2011


(case 2)

if instead of 8/31/2011 this date was 9/31/2011, then we have 3 dates in sheet1 col J that are on or before 9/31/2011

3/16/2011 (listed in I2 of sheet2)
6/15/2011 (listed in I3 of sheet2)
9/21/2011 (listed in I4 of sheet2)
are on or before 9/31/2011


the blue expression would then become

((1 + ((sheet1 I2 - sheet2 $R$8) * (sheet1 K2 / 360))) * (1 + ((sheet1 I3 - sheet2 $R$8) * (sheet1 K3 / 360))) * (1 + ((sheet1 I4 - sheet2 $R$8) * (sheet1 K4 / 360))))

for both cases the green expression is

(1 + ((sheet2 P5 - sheet2 $R$8) * (sheet2 M5 / 360)))

for both cases the red expression is

(sheet2 G5)


so in case 1 the final expression for the date in E5 is

(sheet2 G5) / [ ((1 + ((sheet1 I2 - sheet2 $R$8) * (sheet1 K2 / 360))) * (1 + ((sheet1 I3 - sheet2 $R$8) * (sheet1 K3 / 360)))) * (1 + ((sheet2 P5 - sheet2 $R$8) * (sheet2 M5 / 360))) ]

in case 2 the final expression for the date in E5 would be


(sheet2 G5) / [ ((1 + ((sheet1 I2 - sheet2 $R$8) * (sheet1 K2 / 360))) * (1 + ((sheet1 I3 - sheet2 $R$8) * (sheet1 K3 / 360))) * (1 + ((sheet1 I4 - sheet2 $R$8) * (sheet1 K4 / 360)))) * (1 + ((sheet2 P5 - sheet2 $R$8) * (sheet2 M5 / 360))) ]


the basic formula for each date in col E of sheet 2 is

Red/ (blue* green)

the blue expression is dynamic because depending on the date in sheet2 col E the number of dates in sheet1 col J that are on or before could be different.

after a Red/(blue* Green) result has been found for each date in sheet2 col E then each one of the Red/(blue* Green) result is added together and this result is shown in cell S8 of sheet2

1. how is the location of S8 affected when the data is updated - presumably it moves ?
the final answer will always be in S8, the location of S8 is not affected when the data is updated

2. does the volume of data on Sheet1 fluctuate also ?
i may enter additional rows in sheet 1 or delete rows every once in a while, but i would do that manually.....it has nothing to do with the updates in sheet2

let me know if this helps

thanks

yes, the sum of X5:X8 is accurate...
im not sure how the array works but the results are accurate...