Querying Info inside excel
 

I want to query information within my excel workbook. This information has been queried externally then a couple of calculated columns have been added that include logic that MS query doesn't support. I then want to query this info. I have tried formulas to pull from these cells but as my query expands or shrinks I keep getting ref# on the formulas where the cells no longer exist. My solution has been to query the query including the extra columns. However when I distribute my file this 2nd query file path breaks down. Are there any better solutions?

Querying Info inside excel
 

I've developed many projects like what you've described. I usually use
vba to calculate/filter/manipulate/format/write the values I want from
the query sheet to a report sheet.

If your report is static, just convert everything to values and
eliminate the links before distribution.
If you want your workbook to be dynamic, you have to add some controls
for the user to refresh the queries and create the output.

In essence, you'll be creating a small "application" with a user
interface (forms/controls), a container (the workbook), and output (the
finished report).

Querying Info inside excel
 

I would avoid querying a querytable in the same file.
For one thing, I think it will be querying the copy of the file on
disc, not the open version in which you have just refreshed the first
query in.

I would refresh the first query using a macro, give its result range a
name and have your formulas reference this range in VLOOKUPs to find
the relevant data.

The macro might be something like:

With Worksheets("WhereThe1stQueryIs").Range("A1").Query Table
.Refresh BackgroundQuery:=False
With .ResultRange
.Resize(,.Columns.Count+2).Name = "QueryPlus"
End With
End With

Then your formulas can do VLOOKUPs in the range QueryPlus which will be
the results of the query plus the extra columns.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup