queryTable

[ltworf]

Never used QueryTables before need help. I have 1 workbook with to sheets.
A datasheet and a reportsheet. the datasheet has field names:
Dept, Location, Skill, Calls Offered, & BAHT.

So lets say in the reportsheet on Cell A1 I need a specific Dept,Location summary from teh datasheet. How do I use QueryTables to get the data.

BTW the calculation I would need is Sum(Calls Offered *BAHT)/sum(Calls Offered) in the select query

 

[SkipVought]

Hi,

You would SUM on [Calls Offered] & BAHT

and specify CRITERIA for Dept & Location.

If you want to make it a Parameter query (so that you can supply the criteria values, then enter
[tt]
[what calls?]
[what BAHT?]
[/tt]
in the criteria and then specify how you want to supply the values in the Parameters window, after you File > return data to Excel.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ltworf]

not sure I understand. could you please give me an example. I'm seeing allot of example on the net but i don't get it. see exampl bit below:

Set QT = WSW.QueryTables.Add(Connection:=ConnectString, Destination:=WSW.Range("A1"))
With QT
.Name = "portfolio"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
End With

 

[SkipVought]

I should have asked you, what application you are in and how you established your query table.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ltworf]

no problem. I'm using excel.
The data I want is on the same workbook as the report itself.

 

[SkipVought]

I see. I always set up my QueryTables via Data > Get external data...

I see that you are using a WEB query. There is no SQL * no Parameters. The selected table values are returned to the QT on the sheet.

Once the data is returned, you could use the Data > AutoFilter to select the criterial you want to zero in on.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}