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]Thanks for your help![/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

'Dim Conn As New ADODB.Connection
'Dim rs As New ADODB.Recordset
'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
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("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``````

1,106,315
Messages
5,510,553
Members
408,798
Latest member
jitu20feb

This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...