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.

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.