Create an annual summary chart based on expiry dates from another sheet?

[oldschooljunkie]

Hi...I'm a new member and I have pretty basic Excel knowledge. I've never dealt with Excel macros or VBA codes. My problem may have a very simple solution...but it alludes me at the moment.

I am trying to simplify an existing spreadsheet that I update on a daily basis to track Tenant Insurance information and expiry dates. From that information, I need to create a seperate sheet to summarize all the expiration dates for the entire year in an easy to read version for the Landlord.

Is there an easy formula that I can input to have the expiry date information populate automatically under the month that it will expire?

I've attached a few screen shots. Hope you can help! Thanks in advance! (and sorry for the poor quality screen shots!)

[Krishnakumar]

Hi,

Welcome to board!!

In C2 on Summary and copied down & across,

=IFERROR(INDEX('Insurance Information'!$D$2:$D$24,MATCH(1,IF('Insurance Information'!$A$2:$A$24=$A2,IF('Insurance Information'!$B$2:$B$24=$B2,IF(TEXT('Insurance Information'!$D$2:$D$24,"mmmm")=C$1,1))))),"")

It's an array formula. To Enter the array formula hold down Ctrl and Shift while pushing Enter.


Note: Adjust the range. Format the cell as date.

HTH

[oldschooljunkie]

Worked like a charm. Thanks soooooo much Kris! You're amazing! You just saved me from wasting my time on redundant spreadsheets! Thanks!