Lookup expiration dates by calendar month

K

Ken King

I have a worksheet tab with the following three columns sorted by Expire Date
with over 500 rows of data.
A B C
Expire Suite #
Date Occupant RSF
3/31/2009 105: Name A 100
1/10/2010 210: Name B 200
2/15/2011 107: Name C 150

I have a 10 year calendar on another worksheet tab (example below) for
presenting expirations by month/year with Suite #: Occupant and RSF (rentable
square feet).

A B C D E
F...
1/1/2009 2/1/2009 3/1/2009â€¦ 12/1/2009
105: Name A 100

1/2/2010 2/1/2010 3/1/2010â€¦ 12/1/2010
210: Name B 200

1/1/2011 2/1/2011 3/1/2011â€¦ 12/1/2011
107: Name C 150

I am trying to write a formula or function to find expiration dates each
calendar month for the 10-year period and return the â€˜Suite #: Occupantâ€™ in
the column below the date and â€˜RSFâ€™ in the adjacent column. Is it possible
to do a lookup for a calendar date i.e. 1/1/2009 to 1/31/2009 etc. and return
the corresponding 'Suite #: Occupant' and 'RSF' columns?
Thanks,
Ken

N

N harkawat

You could easily get this info using a Pivot table. Once you have the dates
right click on a date on the Pivot table and select "group and show
detail"--> group and select month+Year
Google it if you are unsure how to create a Pivot table

K

Ken King

N harkawat said:
You could easily get this info using a Pivot table. Once you have the dates
right click on a date on the Pivot table and select "group and show
detail"--> group and select month+Year
Google it if you are unsure how to create a Pivot table

I don't think pivot table will work since I want a detailed report of all my
data by each calendar month for 10 years.

S

Shane Devenshire

Hi,

How are you going to handle split rentals - JJ's rental goes through 1/15/09
and then Q takes it over on 1/16/09?

S

Shane Devenshire

Hi,

Think I need to add a second question, for any one month with two different
properties leases expiring how do you show them. Its starting to look like a
macro will be you only solution unless only one suite/occpt. can occur in an
one month/year.

In that case try a setup like this, where this is the range D1:H6. (you
would extend it to the right to cover all 12 months.

1/1/2009 2/1/2009 3/1/2009 4/1/2009
1/1/2009 105:NameA 100
1/1/2010 210:NameB 200
1/1/2011 107:NameC 150
1/1/2012
1/1/2013

The formulas starting in E2 are

=IF(SUMPRODUCT(--(YEAR(\$D2)=YEAR(\$A\$2:\$A\$4)),--(MONTH(E\$1)=MONTH(\$A\$2:\$A\$4)),ROW(\$A\$2:\$A\$4)-1)<>0,INDEX(\$B\$2:\$B\$4,SUMPRODUCT(--(YEAR(\$D2)=YEAR(\$A\$2:\$A\$4)),--(MONTH(E\$1)=MONTH(\$A\$2:\$A\$4)),ROW(\$A\$2:\$A\$4)-1))&"
"&INDEX(\$C\$2:\$C\$4,SUMPRODUCT(--(YEAR(\$D2)=YEAR(\$A\$2:\$A\$4)),--(MONTH(E\$1)=MONTH(\$A\$2:\$A\$4)),ROW(\$A\$2:\$A\$4)-1)),"")