Extracting data onto summary page and sorting by expiry date

[jiggaman84]

Hello out there,

I have two sheets. The first sheet is list of all tenants in a portfolio of a hundred properties (thousands of tenants). Each line has Name of Property (column A), tenant name (B), square footage of tenant (C), rent and expiry date (D).

The second sheet is a summary page that list the various info described above that gets populated based on the property name (I use a drop down menu here located in 'Summary Template'!$A$2).

What I am trying to do is extract all the tenants who are expiring in 2013 and show them in a list sorted from earliest date to latest.

I have been able to extract the data no problem with index/match/small formula but I can't seem to figure out how I can get a formula to sort it. I've been told to use Rank but can't seem to make it work.

FOR MY PURPOSES I CANNOT USE PIVOT TABLES, ADVANCED FILTERING, OR SORT THE FIRST SHEET BY DATE.

Can i modify my current formula to automatically display the tenant by earliest expiry date?

Here is the formula I am using:

=IFERROR(INDEX(Sheet1!$E$1:$CC$135,SMALL(IF((Sheet1!$E$30:$E$135='Summary Template'!$A$2)*(Sheet1!$R$30:$R$135<=DATE(2013,12,31))*(Sheet1!$R$30:$R$135>=DATE(2013,1,1))*(Sheet1!$R$30:$R$135>0),ROW(Sheet1!$E$30:$E$135)),ROW(1:1)),MATCH(E$23,Sheet1!$E$29:$AD$29,0)),"")

Thanks in advance,

jiggaman84

 

[Akashwani]

Hi,

Do you really have the Rent and the Date in the same cell?

Here is a "sample" worksheet for you to look at to see if it gives you some ideas, I hope it helps.

jiggaman84.xls - 4shared.com - online file sharing and storage - download

Good luck.

Ak