conditional lookup macro looping

xelhelp

New Member
Joined
Mar 6, 2011
Messages
28
hi,

in cell S8 of sheet2 im trying to do a calculation

in sheet1...

i have values in sheet 1 in column I, I2:I42
i have dates in sheet 1 in column J, J2:J42
i have values in sheet 1 in column K, K2:K42

in sheet2...

i have values in sheet 2 in column M, P, and G and dates in column E all of this data starts at row 5 going down X number of rows, however they will all go down the same number of rows, X because the external feed will update and I don't know how many rows it will populate

the dates in cell E5 of sheet2 could be entered anywhere between E5:EX depending on the update

i have a single value in cell R8 of sheet2
the final answer of the calculation would be in cell S8 of sheet2

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) * (value 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) * (value 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 blue 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... if we had four dates in column E in sheet2 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

thanks, any help is appreciated
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

xelhelp

New Member
Joined
Mar 6, 2011
Messages
28
i think it will require some sort of nested loops, with next statements but im not sure how to get started...
 

Forum statistics

Threads
1,143,673
Messages
5,720,223
Members
422,270
Latest member
CaptainMurray

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top