Return multiple values from another table if cell is within next 1.5 years

[kfryar88]

Hi all,

This is my first time posting so apologies if i don't know the lingo!

I have a property register set up as a table. Some properties have lease expiries (however this field is blank for a lot of properties), on a separate tab I want it to display each row with an expiry coming up within the next 1.5 years.

TAB ONE TABLE:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Property[/TD]
[TD]Expiry[/TD]
[TD]Owner[/TD]
[TD]Owner Contact[/TD]
[TD]Tenant[/TD]
[TD]Tenant Contact[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
(it has a lot more columns but this is just an example)


On a separate tab, I want a second table/list to only pull info from rows that have the "Expiry" column date <(TODAY()+550)

Hope that makes sense!!! I figure it would be an array formula? but not sure how to do it.

Thank you!

 

[FDibbins]

I put this on the same sheet, but you can move the extract table to where you need it to be. Also, I just used a simple date, to keep the table manageable. See if you can adaptthis to your needs - get back to me if you need more assistance

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Name[/td][td]Date[/td][td][/td][td]

5/1/2016​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]AA[/td][td]

1/1/2016​

[/td][td][/td][td][/td][td]EE[/td][/tr]

[tr][td]

3​

[/td][td]BB[/td][td]

2/1/2016​

[/td][td][/td][td][/td][td]FF[/td][/tr]

[tr][td]

4​

[/td][td]CC[/td][td]

3/1/2016​

[/td][td][/td][td][/td][td]GG[/td][/tr]

[tr][td]

5​

[/td][td]DD[/td][td]

4/1/2016​

[/td][td][/td][td][/td][td]HH[/td][/tr]

[tr][td]

6​

[/td][td]EE[/td][td]

5/1/2016​

[/td][td][/td][td][/td][td]II[/td][/tr]

[tr][td]

7​

[/td][td]FF[/td][td]

6/1/2016​

[/td][td][/td][td][/td][td]JJ[/td][/tr]

[tr][td]

8​

[/td][td]GG[/td][td]

7/1/2016​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]HH[/td][td]

8/1/2016​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]II[/td][td]

9/1/2016​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]JJ[/td][td]

10/1/2016​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]
D1=your date
E2=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$11>=$D$1,ROW($A$2:$A$11)),ROWS($A$1:A1))),"")
this is an ARRAY formula, entered using CTRL SHIFT ENTER, not just enter
Then copy down as needed.

Instead of a fixed date as I used, you could, instead, use this...
=EDATE(TODAY(),-18)
where -18 is 1.5 years 18 months

 

[kfryar88]

Thanks Ford!

That worked a treat!

Another question for you... i might be pushing my luck here...

If i had multiple property registers (e.g. a tab for each suburb) would i be able to look up each tab and return the expiry criteria in one list?

 

[FDibbins]

Yes, sure, you would just need to include the specific sheet name in each "set" of formulas. OR...you could have a list of sheet names, and then reference them in the formula by using INDIRECT

 

[kfryar88]

I seem to be struggling, i thought it was working because it picked up one of the properties, but not all. this is what i am inputting on Sheet2 A4:

=IFERROR(INDEX(Table1[@Ref],SMALL(IF(Table1[@Expiry]<=EDATE(TODAY(),18),ROW(Table1[@Ref])),ROWS($A$1:A1))),"")

Table1[@Ref] = Column of Property Addresses (which is what i want to return)
Table1[@Expiry] = Column of Expiry dates

Sorry to be a pain!

 

[FDibbins]

I don't like working with tables because the references can be hard to follow. You said you wanted to look up data from different sheets, so you need to put that sheet name in the formula

 

[Aladin Akyurek]

I seem to be struggling, i thought it was working because it picked up one of the properties, but not all. this is what i am inputting on Sheet2 A4:

=IFERROR(INDEX(Table1[@Ref],SMALL(IF(Table1[@Expiry]<=EDATE(TODAY(),18),ROW(Table1[@Ref])),ROWS($A$1:A1))),"")

Table1[@Ref] = Column of Property Addresses (which is what i want to return)
Table1[@Expiry] = Column of Expiry dates

Sorry to be a pain!

Does this work for you?

In A4 of Sheet2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Table1[Ref],SMALL(IF(Table1[Expiry]<=EDATE(TODAY(),18),ROW(Table1[Ref])-ROW(INDEX(Table1[Ref],1,1))+1),ROWS($A$4:A4))),"")

 

[kfryar88]

I managed to get it working now with one sheet. my formula is as below.

=IFERROR(INDEX('Seven Hills'!A:A,SMALL(IF('Seven Hills'!$Z$3:$Z$5000<=EDATE(TODAY(),18),ROW('Seven Hills'!$A$3:$A$5000)),ROWS($A$2:A2))),"")

My issue now is for it to look up the same array but in multiple sheets. I have no idea who INDIRECT works, sorry to be a pain!