How about you use DataFilterAutofilter on Sheet2 then copy the visible cells
to Sheet1

See Debra Dalgleish's site for programming with autofilter.

http://www.contextures.on.ca/xlautofilter03.html

Where you will find this.

http://www.contextures.on.ca/xlautofilter03.html#Copy


Gord Dibben MS Excel MVP


On Fri, 2 Nov 2007 10:06:10 -0700, "Irvine, Dennis"
wrote:

I have a table of data in Sheet2.

Depending on a users input into Cell a1 on Sheet1, I would like to display,
on Sheet1, a subset of the data in the table in sheet 2.

so if a user inputs an "A" in Cell A1 on Sheet1, I want a list of data to
show up on sheet1 that is all the rows of data from sheet2 that relate to
"A".

I can do what I want by having a seperate workbook with the table data in it
and using an External Data query to bring in the data. But I can't query on
a table in the main workbook itself.

Being able to Query on External data, but not data in the open workbook
seems crazy to me, so I must be missing something.

Is there a way to query on a data table in one worksheet and have the
results show on another worksheet.

Thanks for any suggestions.