Subset of table data on another worksheet

[Irvine, Dennis]

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.

 

[Gord Dibben]

How about you use Data>Filter>Autofilter 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