Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook?

[Lisa B.]

Wow! I tried this out, and it seems to work beautifully. It's odd, though,
that I haven't seen this technique mentioned in any of the several Excel
references that I've looked at.

Basically, I have a workbook with several worksheets...one worksheet
contains a large list with all the records.

I wanted to set up the other worksheets to have certain functions...I wanted
them to use only certain columns from the main list, and to contain only
certain records from the main list that met specified conditions.

This is the best solution I have found so far - i.e., creating database
queries on these other worksheets which query the main list.

But, whenever I see query discussed in the reference books, they always
discuss it in terms of querying an "external" data source...I've never seen
them mention querying another page in the same workbook. It makes me think
maybe I'm overlooking some other very easy, obvious, and direct way of doing
what I want to do.

Anyone else out there have any other clever ways of creating refreshable
subsets of an excel list?

Lisa B.

[Ron Coderre]

I agree! If you're comfortable with basic SQL, MS Query can be a powerful
tool to harvest data from Excel. I use it fairly regularly against the same
and multiple workbooks for: Merging data, Extracting same/different data,
etc. Many times a MS Query solution can be built in a fraction of the time
it would take for me to code a VBA solution.

To see some of my forum responses that pertain to MS Query, search the
general Excel forum for "MS Query Coderre".

***********
Regards,
Ron


"Lisa B." wrote:

>
> Wow! I tried this out, and it seems to work beautifully. It's odd, though,
> that I haven't seen this technique mentioned in any of the several Excel
> references that I've looked at.
>
> Basically, I have a workbook with several worksheets...one worksheet
> contains a large list with all the records.
>
> I wanted to set up the other worksheets to have certain functions...I wanted
> them to use only certain columns from the main list, and to contain only
> certain records from the main list that met specified conditions.
>
> This is the best solution I have found so far - i.e., creating database
> queries on these other worksheets which query the main list.
>
> But, whenever I see query discussed in the reference books, they always
> discuss it in terms of querying an "external" data source...I've never seen
> them mention querying another page in the same workbook. It makes me think
> maybe I'm overlooking some other very easy, obvious, and direct way of doing
> what I want to do.
>
> Anyone else out there have any other clever ways of creating refreshable
> subsets of an excel list?
>
> Lisa B.
>

[jocke]

wes i have tried that before. linking to excelsheet with a query, but i have never succeded to "read" from the same workbook, only reading in closed workbooks.

tell me how did you do to set a query to the same workbook.

/jocke

[jocke]

wes i have tried that before. linking to excelsheet with a query, but i have never succeded to "read" from the same workbook, only reading in closed workbooks.

tell me how did you do to set a query to the same workbook.

/jocke

[Ron Coderre]

>First, set up your data tables and assigned range names to them.
>Save the workbook before you try to set up the query against it.
(You don't need to close the workbook)
>Set up the query in the current workbook referenceing the tables in the same workbook. Note: You will need to point to the network location of the saved file, but Excel will use the data in the open version.

That's about it.

Does that help?
Ron