# Using ALL to return a table

#### juliengirard

##### New Member
[FONT=&quot]Hello,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I have a Fact table like this:[/FONT]

 [FONT="]Product[/FONT] [FONT="]Customer[/FONT] [FONT="]Region[/FONT] [FONT="]Date[/FONT] [FONT="]Sales[/FONT]

<tbody>
</tbody>
[FONT=&quot]
In the middle of a calculation, I would like to put that table in a variable with the following filter:
[/FONT]

 [FONT="]Based on context[/FONT] [FONT="]Based on context[/FONT] [FONT="]Based on context[/FONT] [FONT="]Any date[/FONT] [FONT="]Based on context[/FONT] [FONT="]Product[/FONT] [FONT="]Customer[/FONT] [FONT="]Region[/FONT] [FONT="]Date[/FONT] [FONT="]Sales[/FONT]

<tbody>
</tbody>
[FONT=&quot]

ALL seems to be the right function but I only manage to use it in a calculation with formula like this:[/FONT]
[FONT=&quot]CALCULATE(
SUM(Fact[Sales] ,
All(Fact[Date]))

[/FONT]

[FONT=&quot]How could I use it to return the table above?[/FONT]

[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### pbornemeier

##### Well-known Member
You can use SQL to return a portion of your table. You will have to modify the SQL string to meet your requirements. If there are any spaces in your header cells or worksheet name, replace their spaces with underscores in the SQL string. The name of the worksheet that holds the table (in the FROM part) must be followed by a dollar sign.

Code:
``````Option Explicit

Sub CreateKEAsOf(Optional dteData As Date)

Dim sSQLString As String
Dim aryReturn As Variant
Dim sDBPath As String
Dim sConnect As String

'Above 2 lines replaced by next 4 to allow late binding
Dim conn As Object  ' As ADODB.Connection
Dim rs As Object  ' As ADODB.Recordset

'Your SQL Statement (Table Name=Sheet Name, coded as: [Sheet1\$])
'Don't forget ending space for all-but-last row in multi-line statements

sSQLString = _
"SELECT * " & _
"FROM [WorksheetName\$] "

sDBPath = ThisWorkbook.FullName
'You can provide the full path of your external file as shown below
'sDBPath ="C:\InputData.xlsx"
sConnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & sDBPath & ";HDR=Yes';"
'If any issue with MSDASQL Provider, Try the Microsoft.Jet.OLEDB:
'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
conn.Open sConnect
rs.Open sSQLString, conn
'=>Load the Data into an array
aryReturn = rs.GetRows

End Sub``````