Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

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

1. I agreed to these rules
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!)

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,682

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. I agreed to these rules
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

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

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