Kerrie,

On your Sheet1, in cell A2, array enter the formula (enter using Ctrl-Shift-Enter)

=IF(COUNTA('DataBase Extract'!$J$2:$J$5000)=ROW()-ROW($A$2)+1,INDIRECT("'DataBase Extract'!" &
ADDRESS(LARGE(('DataBase Extract'!$J$2:$J$5000<"")*ROW('DataBase
Extract'!$J$2:$J$5000),COUNTA('DataBase
Extract'!$J$2:$J$5000)+ROW($A$2)-ROW()),COLUMN()-COLUMN($A$2)+1)),"")

Then copy down for 4000 or so rows....If you could possilby have more than 5000 records extracted,
then increase the 3 instances of 5000 to some other larger number.

Note that this formula is written so that it can be copied into column B, C, etc, to extract those
values as well from sheet1, in case you need more than just column A.

HTH,
Bernie
MS Excel MVP


"Kerrie Wood 45693" wrote in message
...
I am having major dramas trying to figure out how to get the information in
my dropdown list to show unly filtered data. I will try to explain what I am
doing and hopefully someone can help me. By the way I have no idea how to do
Macros or any VB stuff so Please if you have a solution an easy explanation
would be appreciated.

I have about 50 columns of data and about 4000 rows in sheet 2. This is a
database auto refresh query I have created. The data in this sheet needs to
show in sheet 2 as it is refreshed each time but only column A needs to be
seen based on the criteria in column J having something in it (NONBLANK) so
basically if there is nothing in column J I do not want the data to show up
in the list on sheet 1.

I have done a basic filter on sheet 2 but if I try to create a link on sheet
1 to column A so all the names show I am still getting all the filtered
(BLANK) cells show up. I know I can just copy the information over once I
have done the filter but this is not going to help me as I need it to auto
refresh each time the cells change.

Is there an easy way to filter from one sheet to another and have it so the
criteria is to only show names from column A that are not blank in column J?

I really hope waht I am asking makes sense.

Tahnks