on 2007 Jun 12 8:36 PM
Hello,
I run BEx queries in workbooks and then use Excel formulas ( mostly VLOOKUP(LookupValue, ResultsArea, ColumnReturned,) ) to retrieve certain information from the query ResultsArea.
****Problem:
Depending on the query parameters, the query "Results Area" is larger or smaller.
****Non-optimal solution:
One way to define the ever changing Excel range occupied by the Results Area is to create an Excel name as:
QueryRange = OFFSET(QueryFirstCell,,,COUNTA(OFFSET(Sheet1!$A:$A,,COLUMN(QueryFirstCell)-1,,)),COUNTA(OFFSET(Sheet1!$1:$1,ROW(QueryFirstCell)-1,,,)))
Then, the only input required is the top-left cell of the Results Area, which stays the same (unless the query definition changes).
****Question
What is the best way to refer to an ever-changing query Results Area in an Excel formula?
Thank you!
Louis
No VBA is required to retrieve a BEX query result range.
Each Excel workbook containing a BEx query has a sheet named "SAPBEXqueries". That sheet can be unhidden by going in the Visual Basic Editor.
That sheet contains sheet-specific range names. The name "SAPBEXq000_" ("_" being the query number) refers to the result area.
Louis
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
It is actually much easier than that.
Use a VBA statement like the following:
Set RptRng = ActiveCell.CurrentRegion
This will retrieve the data in the Results Area, no matter what the size is.
Hope this helps...
Bob
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
73 | |
10 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.