Validation List

[evan.winstanley]

Hi,

I have a table of data where the value in colum A will be either "Yes",
"No" or "Maybe". In column B will be a company name.

On a second sheet in a seperate table, I want to have a validation
condition on one of the columns that will show all the company names in
column B from table 1 where the corresponding value in column A is Yes.

I cant do a normal validation list like $B$2:$B$100 because that will
not filter out where A is not equal to Yes.

I also can't use a lookup table, because that will only return the
first Yes it comes across.

Any ideas?

 

[VBA Noob]

Hi,

Think this website should help

http://www.contextures.com/xlDataVal02.html

VBA Noob

 

[evan.winstanley]

I've already had a look at that site and its not really the same thing.

For that to work my data would have to be represented something like
this:

Yes Maybe
abc jkl
def mno
ghi pqr

but my data is not like that, it is like this

Yes abx
Yes def
No jkl
No pqr
No mno
Yes ghi

 

[Bob Phillips]

You need to create another list of company names of Yes.

Select a range the same size as the company names (I assume 20 here) and
enter this formula in the formula bar

=IF(ISERROR(SMALL(IF($A$1:$A$20="YES",ROW($B1:$B20),""),ROW($B1:$B20))),"",
INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20="YES",ROW($B1:$B20),""),ROW($B1:$B20)))
)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)