Create table by querying other table

[sparkyrose]

Hi All,

This seems like it should be a simple thing but I just can't figure it out.

I have a table of data (call it the Master) in one worksheet. I have another sheet in the same workbook which is acting as a one-page summary (I will print and give to my boss).

I want to create a small table on the summary page by selecting columns and rows from the Master, based on a specific parameter. If I was working in Access or SQL I'd be fine (SELECT, columnA, columnB, WHERE ...). I can't for the life of me see how to do this in Excel.

Any ideas?

I thought about vlookup and pivot tables but neither really seems appropriate; vlookup because the user has to enter the matching data point and pivot table because I'm not manipulating the data, just selecting it.

Thanks in advance for any suggestions.

 

[AlphaFrog]

Autofilter?

Excel Filters: Excel 2007 AutoFilter Basics

 

[xenou]

I thought of autofilter too ... or just formulas (despite the drawbacks). Querying a workbook on itself was a problem in Excel 2003, creating memory leaks. I'm not sure if anything's improved on that in recent versions. Of course you can always try it and see ... (just keep a backup in case you need to rebuild it - I did this once and it was okay for a few months then crashed).

If you do query the data, it's best to do it in two workbooks, one for the data and one for the report.

Note: this was the support article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319998
(a workbook querying itself is, by definition, an open workbook).

 

[sparkyrose]

Thanks for the comments.

The autofilter works fine, except that I really wanted to have three of these brief summary tables. I realized after creating the second, that you can't have more than one active filter on a sheet, even if they are not side by side.

It seems like the other option is to put these into separate files and make one the data source for the other.

Thanks again!

 

[AlphaFrog]

You could autofilter your data and copy just the filtered data to different areas or sheets. Do that for each summary table.