Drop down list from filtered data

[Brady]

Howdy,

I need to create a drop down list. The cell used to create the
data/validation in is on "Sheet 1". The list is on "Sheet 2".

Here are the specifics:
Sheet 2 list contains the following columns: column 1 = Structures,
column 2 = Guyed
The list can grow or shrink and the values can change.

Example data:
Structure Guyed
1
2 Y
3 Y

When filtered for "Guyed" structures only, the list becomes:
Structure Guyed
2 Y
3 Y

Now on Sheet1 I have a single cell that I want users to be able to
click and select a single Guyed structure. Thus, the drop down list
must only contain those structures with a "Guyed" value = Y (and don't
forget, those values can change along with the structure numbers and
thus, it must be dynamic not static).

I appreciate any guidance.
Best regards,
Brady

 

[Guest]

One play ..

In Sheet2,

Table in cols A and B, data from row2 down to say, a max expected row100

Use 2 empty cols to the right, say, cols D & E

In D2:
=INDEX(A:A,MATCH(SMALL(E:E,ROW(A1)),E:E,0))

In E2:
=IF(B2="","",IF(B2="Y",ROW(),""))
(Leave E1 empty)

Select D2:E2, copy down to E100 to cover the max expected data
Col D will return the "guyed" items from col A, bunched neatly at the top

Then create a dynamic, defined range to grab col D's items

Click Insert > Name > Define, and set it as:
Names in workbook: Guyed
Refers to: =OFFSET(Sheet2!$D$2,,,COUNT(Sheet2!$E:$E))

Then in Sheet1, create the DVs as desired ..

Select B2:B10 (say)
Click Data > Validation
Allow: List
Source: =Guyed
Click OK

The DV droplists' selections will be dynamic as required, ie display only
those items in Sheet2's col A flagged as "Y" in col B

 

[Brady]

Max,

I'm having a little difficulty with the solution. Here is what I've
done to make it workin my scenario.

First the actual data:
The name of the items I want in the list are in column A (beginning at
A3).
The column for "guyed" is P.
The two end columns I am using for your example below are AC (you used
D) and AD (you used E).

What I've changed in your formula:
In cell AC3 I have the following:
=INDEX(A:A,MATCH(SMALL(AD:AD,ROW(A3)),AD:AD,0))

In cell AD3 I have the following:
=IF(P3="","",IF(P2="Y",ROW(),""))

With this scenario, I get the error #NUM! in cell AC3.
When evaluating, it is choking at (SMALL(AD:AD,ROW(A3)) so...I assume
either the (A3) is the incorrect value for "ROW" or the AD:AD is
incorrect.
Can you help?
***********************************************

 

[Brady]

Max,

Nevermind. I figured it out! I'm still not sure how it works but I
did figure out how to make it do what I want.

I changed
=INDEX(A:A,MATCH(SMALL(AD:AD,ROW(A3)),AD:AD,0))
To
=INDEX(A:A,MATCH(SMALL(AD:AD,ROW(A1)),AD:AD,0))

AND
=IF(P3="","",IF(P2="Y",ROW(),""))
To
=IF(P3="","",IF(P3="Y",ROW(),""))

....and all is well.

Thanks again!

 

[Guest]

Glad you sorted it out, Brady.

=INDEX(A:A,MATCH(SMALL(AD:AD,ROW(A3)),AD:AD,0))
To
=INDEX(A:A,MATCH(SMALL(AD:AD,ROW(A1)),AD:AD,0))

Yes, that's right. Use ROW(A1) for the formula in the starting cell,
irrespective of where this cell may be. ROW(A1) is used here as an
incrementer, to return the sequential series: 1, 2, 3 ... as we copy the
formula down. To see this, just put in any cell: =ROW(A1), then copy down.
To propagate likewise when copying formulas across, we could use COLUMN(A1).

---

 

[Brady]

What's that...could it be...yes, yes...I think it is. The cloud is
lifting and I can see the trees again!

Thanks Max. I think I understand most of what is happening in the
formula!

 

[Guest]

Glad to hear that, Brady!