Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

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

  1. #1
    Join Date
    17th February 2011
    Posts
    2

    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 Images

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,326

    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

  3. #3
    Join Date
    17th February 2011
    Posts
    2

    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!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: July 7th, 2011, 07:03
  2. Extracting Records Based on Expiry Dates
    By Talk_Is_Cheap in forum EXCEL HELP
    Replies: 2
    Last Post: January 12th, 2010, 09:34
  3. Summary Report For Quarters Based On Dates
    By shudder in forum HIRE HELP
    Replies: 45
    Last Post: October 8th, 2009, 01:25
  4. Create Pie Chart From Table Summary Results
    By binar in forum EXCEL HELP
    Replies: 13
    Last Post: July 23rd, 2009, 23:32

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno