Extract data based upon Yes/No condition

**dramadog** · 06-07-2012, 01:25 PM

Good afternoon everyone. I'm preparing a budget for an office building for 2013. I think my problem is fairly simple but I'm stumped (see attached).

I have a spreadsheet which contains a current rent roll for a property. I've inserted a Yes/No data validation in column U of the Rent Roll tab, which is intended to identify whether the tenant is projected to renew its lease (assuming the lease expires sometime during 2013). Ideally, the data validation would not be able to be changed unless the year that the lease expires (column I) equals 2013, but I've not figured out how to do that yet.

My goal is to extract the tenant name, Suite number, & Sq Ft from the Rent Roll tab onto the 2013 Renewal tab if the answer in column U from Rent Roll tab = Yes for each tenant. The 2013 Renewal tab has several sections for such tenant info, some of which will be blank depending on how many tenants are expected to renew their leases. For the moment, I've just linked the data I'm trying to pull on the 2013 Renewals tab and have highlighted the fields I want to insert in yellow.

I really, really appreciate your help in advance!!!

**dramadog** · 06-07-2012, 03:49 PM

I see the Admin moved my question from Excel 2007 to Excel Programming section...I'm very hopeful that there is an answer that does Not involve VBA, as I'm a newbie in that regard.

**NBVC** · 06-07-2012, 04:33 PM

For the data validation, perhaps this is a workaround to VBA...

Enter the variables, Yes and No in two consecutive cells somewhere else in the sheet, say Z2 and Z3, then change validation source to formula like"

=IF(YEAR($I8)<>2013,$Z$1,$Z$2:$Z$3)

where Z1 is a blank cell, Z2:Z3 contain the yes/no.

If the year in column I is not 2013, you will have no choices in the drop down.

Now for the data extraction.

In Rent Roll add a helper column, say in column K.. in K2 enter formula:

=IF(U8="yes",COUNT(K$7:K7)+1,"")

and copy down.

Then in the other sheet, in A6 use formula to extract first:

=IFERROR(INDEX(RentRoll!$C$8:$C$14,MATCH(1,RentRoll!$K$8:$K$14)),"")

in C6: =IFERROR(INDEX(RentRoll!$G$8:$G$14,MATCH(1,RentRoll!$K$8:$K$14)),"")

in D6: =IFERROR(INDEX(RentRoll!$E$8:$E$14,MATCH(1,RentRoll!$K$8:$K$14)),"")

in the A21, C21, D21, use same formula changing the 1 after MATCH( to a 2, and change to 3 for the next group, etc.. if there are more groups then matches, you should get blanks...

**dramadog** · 06-07-2012, 04:53 PM

Wow, that appears to have worked like a charm! Thank you so much NBVC!!!

I have one follow up question. If I want to amend the validation formula to allow for a Yes/No variable to populate if the year is either 2013 or 2012, how would I do this?

I tried using
=IF(OR(YEAR($I8)<>2013,YEAR($I8)<>2012),$Z$1,$Z$2:$Z$3)
but that didn't seem to work?

**NBVC** · 06-07-2012, 05:01 PM

instead of OR, use AND

**dramadog** · 06-07-2012, 05:11 PM

Perfect, thanks again. Solved!

**NBVC** · 06-07-2012, 05:15 PM

You are welcome...

I just noticed one thing... the formulas I provided should have an 3rd argument in the MATCH() or else you will get repeated info.

so amend to:

=IFERROR(INDEX(RentRoll!$C$8:$C$14,MATCH(1,RentRoll!$K$8:$K$14,0)),"")

and so on