Dropdown list and filtered data

[Kerrie Wood 45693]

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

 

[Bernie Deitrick]

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]

Bernie thank you for your help but I am getting an N/A error when I try the
formula in cell A2
On my sheet 1 I need the information to be placed into row BP as it is the
column I have linked to my data validation. All the other rows have other
data in them. I need the list of names from sheet 2 to show in column BP on
sheet 1 based on the fact that if there is no data in column J on the sheet 2
then the information from column A in sheet 2 does not show up. Below is an
example of how the data on sheet 2 is

A J

Figjam (Blank)
People 10 Carpenter
Pluto (Blank)
Happy 12 Plumber
OMG 15 Electrician

I just want to get the list of names from column A (sheet2) to be sent
through onto sheet1 based on the fact that column J actually has something in
it and is not blank. I need to do this so my dropdown list can show only the
names from sheet2 that have data in column J.

This is driving me nuts!! I really want to do a course in Excel. I know it
can be done I just dont know how to do it! it would be easier if I could
figure out how to actually filter the data from ACT so that when I do the
Import data into sheet 2 only the names I wanted came through but life was
not meant to be easy....

Thanks for your help and I really hope I am not confusing you more.

 

[Bernie Deitrick]

Kerrie,

I cannot get it to give me an NA error... did you change the sheet name in
this part:

INDIRECT("'DataBase Extract'!"

Since it is a string, it does not auto update.

If you cannot get it to work, send me a copy of your file - take out the
spaces and change the dot to .

HTH,
Bernie
MS Excel MVP

 

[Kerrie Wood 45693]

Bernie. No sorry I had not changed the sheet name. I have changed it now and
I am getting the error #NUM!

If it helps my Sheets are Sheet1 (Where I need the list in column BP) Sheet2
where the data extraction is.

 

[Bernie Deitrick]

Kerrie,

In BP2, enter this array formula - You MUST use Ctrl-Shift-Enter and not just Enter to make this
formula work

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

HTH,
Bernie
MS Excel MVP

 

[Kerrie Wood 45693]

You are a LEGEND!!!! Thank you soooooooo much... It Worked!!!...

YAY...*happy dance*

 

[Bernie Deitrick]

You are a LEGEND!!!!

Yes, a legend in my own mind ;->

Thank you soooooooo much... It Worked!!!...

You're quite welcome, and thanks for letting me know that you got it to
work.

Bernie

 

[KC Rippstein hotmail com>]

Bernie,
I just made a new post but thought if there's any chance you are still
subscribing to this post perhaps you'd see this first. I have the same exact
situation as reflected below, but I cannot get your array formula to work for
me.

Instead of Sheet2!A2:J5000, my range is Termd!A4:E103 (person in A, status
in E).
Instead of "non-blanks", I need all status of "Involuntary" (or all status
of <>"Voluntary", whichever is easier to use)
Instead of Sheet1!BP2 as the destination, mine is Plans!Q4.

Here's what I tried to no avail. Help?
=IF(COUNTA(Termd!$A$4:$E$103)>=ROW()-ROW($A$4)+1, INDIRECT("Termd!" &
ADDRESS(LARGE((Termd!$E$4:$E$103<>"Voluntary")*ROW(Termd!$E$4:$E$103),
COUNTA(Termd!$E$4:$E$103)+ROW($A$4)-ROW()),COLUMN()-COLUMN($Q$4)+1)),"")