Holdings checking

[Trish21]

Hi!

Can you please help to code the following Macros into the attached spreadsheet.

If record date or payable date is between cell G1 to cell H1, return YES in E1
From record with a yes in E1, use ex date in B1
Using exdate in B1, determine if it is between I2 to I3, if so use tab 230608 or if it is within I3 to I4, if so use tab 300608, continue until the last date range
Do a vlookup in the relevant tab and mark yes if it is in the tab, otherwise no.

Thanks

[olasa]

Here is one option (see encl. file).

The logic: =IF(AND(Check1=True;Check2=True);"Yes";"No")
Check1: OR(AND(C2>=$F$2;C2<=$G$2);AND(D2>=$F$2;D2<=$G$2))
Check2: ISNUMBER(MATCH(A2;INDIRECT(VLOOKUP(B2;$H$2:$I$27;2)&"!C2:C100");0)))

Hope this helped
Ola


Notes:
1. The length of the ranges have to be changed to your need.

2. I wasn't sure if the first and the second check should be in the same formula.
But it should be no problem to separate them, if needed.

3. It looked like the sheet could simplified, so I tested to (in a copy)
* remove col. I
* move the Weekly summary date range to the top
* I did not change this but - unless the trailing blanks in the the security code are important, use TRIM()

[arthurbr]

Hi,
as you haven't reacted to mod's request, I am closing this thread.
feel free to open a new thread with a more descriptive title
Cheers