I am trying to make a Validation drop down list with the list source a set of
data from an advanced filter. I can make an extra worksheet to put the
filtered data in before using it as a source for the drop down list. But I
was just wondering if anyone knows of a way to do this directly as a range
formula in the Validation List Source.
Thanks,
RDW

Hi

Take a look at Debra Dalgleish's site for lots of help on DV.
Without more information about what the list looks like, or the filtered
set of data and the criteria, I can't say if you can do what you want
without creating the filtered list first.

But, if you look at the numerous examples Debra has created, you may see
a method to suit you.
http://www.contextures.com/xlDataVal13.html

--
Regards

Roger Govier


"RD Wirr" wrote in message
...
I am trying to make a Validation drop down list with the list source a
set of
data from an advanced filter. I can make an extra worksheet to put the
filtered data in before using it as a source for the drop down list.
But I
was just wondering if anyone knows of a way to do this directly as a
range
formula in the Validation List Source.
Thanks,
RDW

Wow, you are fast Roger. Thanks for the input. I have already looked at
Debra's website, in fact it's one of my favorites when I go looking for Excel
solutions. She has some good techniques for dependent validation lists but I
could find anything about using a filtered dataset as source data.

I have a long list with diverse parts number data in it and I need to filter
a single column in that list for a parts numbers (unique) with a particular
prefix and use that data for a Validation list.

I know I can do an advanced filter to another range and then reference that
range to get the List data but then I have to continually refresh the
filtered data each time I want to use the drop down list. It's possible but
I'm not to brilliant with VBA and it would require some of that to keep
everything updated...I think. Just looking for a formulaic method.

Rgds,
RDW

"Roger Govier" wrote:

Hi

Take a look at Debra Dalgleish's site for lots of help on DV.
Without more information about what the list looks like, or the filtered
set of data and the criteria, I can't say if you can do what you want
without creating the filtered list first.

But, if you look at the numerous examples Debra has created, you may see
a method to suit you.
http://www.contextures.com/xlDataVal13.html

--
Regards

Roger Govier


"RD Wirr" wrote in message
...
I am trying to make a Validation drop down list with the list source a
set of
data from an advanced filter. I can make an extra worksheet to put the
filtered data in before using it as a source for the drop down list.
But I
was just wondering if anyone knows of a way to do this directly as a
range
formula in the Validation List Source.
Thanks,
RDW

Hi

You could try something like the following.
I had my long list of pats in column A, starting at A2
In column B starting at B2 I had
=LEFT(A2,1)

In B2 enter the starting Prefix required

In C2 enter the array formula

{=IF(ISERROR(INDEX(A:A,SMALL(IF($B$1:$B$50=$C$1,
ROW($B$1:$B$50)),ROW(1:1)))),"",
INDEX(A:A,SMALL(IF($B$1:$B$50=$C$1,
ROW($B$1:$B$50)),ROW(1:1))))}

(It is all one long formula, I just broke it at those points to prevent
the newsreader breaking the formula in awkward places)

To commit of edit and Array formula, use Ctrl+Shift+Enter (CSE)
When you use CSE Excel will insert the curly braces { } for you. Do
not type them yourself.

Copy down as required.
Set up a defined Name of MyList to refer to
=OFFSET($C$2,0,0,COUNTA($C$2:$C$100))

Use DV with List = MyList.
Change value in B1 to bring up a different list.

Alter the Left(B2,1) to the number of characters for your prefix.

This might get you started on a solution.

--
Regards

Roger Govier


"RD Wirr" wrote in message
...
Wow, you are fast Roger. Thanks for the input. I have already looked
at
Debra's website, in fact it's one of my favorites when I go looking
for Excel
solutions. She has some good techniques for dependent validation lists
but I
could find anything about using a filtered dataset as source data.

I have a long list with diverse parts number data in it and I need to
filter
a single column in that list for a parts numbers (unique) with a
particular
prefix and use that data for a Validation list.

I know I can do an advanced filter to another range and then reference
that
range to get the List data but then I have to continually refresh the
filtered data each time I want to use the drop down list. It's
possible but
I'm not to brilliant with VBA and it would require some of that to
keep
everything updated...I think. Just looking for a formulaic method.

Rgds,
RDW

"Roger Govier" wrote:

Hi

Take a look at Debra Dalgleish's site for lots of help on DV.
Without more information about what the list looks like, or the
filtered
set of data and the criteria, I can't say if you can do what you want
without creating the filtered list first.

But, if you look at the numerous examples Debra has created, you may
see
a method to suit you.
http://www.contextures.com/xlDataVal13.html

--
Regards

Roger Govier


"RD Wirr" wrote in message
...
I am trying to make a Validation drop down list with the list source
a
set of
data from an advanced filter. I can make an extra worksheet to put
the
filtered data in before using it as a source for the drop down
list.
But I
was just wondering if anyone knows of a way to do this directly as
a
range
formula in the Validation List Source.
Thanks,
RDW

Hi Roger,

Sorry for the delay in responding. Been travelling. Thanks for your help.
This is a clever solution and it works but my only problem is my range of
data is running on for many 1000's of rows and getting bigger all the time.
All these lookups really slow things down.

I am working to reconfigure the way I store the data in the first place and
hopefully will find a more streamlined solution. I will come back again if I
get stuck.

Thanks again,
Rgds,
RDW

"Roger Govier" wrote:

Hi

You could try something like the following.
I had my long list of pats in column A, starting at A2
In column B starting at B2 I had
=LEFT(A2,1)

In B2 enter the starting Prefix required

In C2 enter the array formula

{=IF(ISERROR(INDEX(A:A,SMALL(IF($B$1:$B$50=$C$1,
ROW($B$1:$B$50)),ROW(1:1)))),"",
INDEX(A:A,SMALL(IF($B$1:$B$50=$C$1,
ROW($B$1:$B$50)),ROW(1:1))))}

(It is all one long formula, I just broke it at those points to prevent
the newsreader breaking the formula in awkward places)

To commit of edit and Array formula, use Ctrl+Shift+Enter (CSE)
When you use CSE Excel will insert the curly braces { } for you. Do
not type them yourself.

Copy down as required.
Set up a defined Name of MyList to refer to
=OFFSET($C$2,0,0,COUNTA($C$2:$C$100))

Use DV with List = MyList.
Change value in B1 to bring up a different list.

Alter the Left(B2,1) to the number of characters for your prefix.

This might get you started on a solution.

--
Regards

Roger Govier


"RD Wirr" wrote in message
...
Wow, you are fast Roger. Thanks for the input. I have already looked
at
Debra's website, in fact it's one of my favorites when I go looking
for Excel
solutions. She has some good techniques for dependent validation lists
but I
could find anything about using a filtered dataset as source data.

I have a long list with diverse parts number data in it and I need to
filter
a single column in that list for a parts numbers (unique) with a
particular
prefix and use that data for a Validation list.

I know I can do an advanced filter to another range and then reference
that
range to get the List data but then I have to continually refresh the
filtered data each time I want to use the drop down list. It's
possible but
I'm not to brilliant with VBA and it would require some of that to
keep
everything updated...I think. Just looking for a formulaic method.

Rgds,
RDW

"Roger Govier" wrote:

Hi

Take a look at Debra Dalgleish's site for lots of help on DV.
Without more information about what the list looks like, or the
filtered
set of data and the criteria, I can't say if you can do what you want
without creating the filtered list first.

But, if you look at the numerous examples Debra has created, you may
see
a method to suit you.
http://www.contextures.com/xlDataVal13.html

--
Regards

Roger Govier


"RD Wirr" wrote in message
...
I am trying to make a Validation drop down list with the list source
a
set of
data from an advanced filter. I can make an extra worksheet to put
the
filtered data in before using it as a source for the drop down
list.
But I
was just wondering if anyone knows of a way to do this directly as
a
range
formula in the Validation List Source.
Thanks,
RDW