Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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



    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!)
    Attached Files

  • #2
    Re: How do I create an annual summary chart based on expiry dates from another sheet?

    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
    Kris

    ExcelFox

    Comment


    • #3


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

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

      Comment

      Working...
      X