Vmurrell: /* FXLDataLookup or SDMXPeriod behaves oddly in “vertical mode” */

2026-06-15T13:23:31Z

‎FXLDataLookup or SDMXPeriod behaves oddly in “vertical mode”

Vmurrell: /* FXLDataLookup */

2026-06-15T13:22:21Z

‎FXLDataLookup

Vmurrell: /* FXLDataLookup Please refer to this article. XXXXXXX */

2026-06-15T13:21:11Z

‎FXLDataLookup Please refer to this article. XXXXXXX

Vmurrell: Created page with "Category:Functions Reference FXLData Introduced in H1.3.0.03 (January 2026). =Background= == FXLDataPeriod Formulas == === Context === To date, FXLData has been used pri..."

2026-06-15T13:18:38Z

Created page with "Category:Functions Reference FXLData Introduced in H1.3.0.03 (January 2026). =Background= == FXLDataPeriod Formulas == === Context === To date, FXLData has been used pri..."

New page

[[Category:Functions Reference FXLData]]
Introduced in H1.3.0.03 (January 2026).
=Background=
== FXLDataPeriod Formulas ==
=== Context ===
To date, FXLData has been used primarily to generate data cubes in Excel from a Fusion Registry by applying user‑defined selection criteria. The add‑in issues SDMX REST queries to the Fusion Registry and renders the results as a structured grid, typically one series per row (time across columns) or one series per column, so analysts can work with a familiar worksheet layout.

=== Problem ===
The current workflow is geared to building full grids/cubes from Fusion Registry; getting a single value or a small ad‑hoc slice often means running a broader query and then indexing/filtering the filled grid, which adds unnecessary steps for simple asks.

=== Solution ===
2 New Formulas have been introduced:

==FXLDataLookup Please refer to this article. XXXXXXX ==

==FXLDataPeriod ==

Returns the minimum and/or maximum SDMX periods found on a specified root grid so lookups can use dataset bounds without manual date handling (with sheet‑not‑found error handling).

=== FXLDataPeriod Formula ===
FXLDataPeriod scans an existing root grid on a specified worksheet and returns the SDMX period(s) available in that grid, either the minimum, the maximum, or both, depending on the mode you pass.

===== Syntax =====
FXLDataPeriod(sheetName, [what], [errorMessages])

==== Parameters ====
'''sheetName''' - The worksheet containing the root grid with the expected header/layout
'''what'''
* OPTIONAL, Defaults to BOTH
* MIN - returns the first period
* MAX - returns the last period
* BOTH - returns both the first and last periods
'''errorMessages'''
* OPTIONAL, Defaults to FALSE
* TRUE/FALSE. When TRUE, returns descriptive messages (e.g., “Grid Not Found”).

=== FXLDataPeriod Examples ===
The examples below are based on the following root grid sheet.

[[File:Root-grid.png|1000px]]

===== Minimum period (single value) =====

=SDMXPeriod("RootGrid","MIN")

'''Result''': 2001
Why: Returns the earliest (minimum) SDMX period available on the root grid sheet.

---------------------------------------------------------------------------------------------------------
===== Maximum period (single value) =====

=SDMXPeriod("RootGrid","MAX")

'''Result''': 2003
Why: Returns the latest (maximum) SDMX period available on the root grid sheet.

---------------------------------------------------------------------------------------------------------
===== Range (min and max — two‑cell spill) =====

=SDMXPeriod("RootGrid")

'''Result''' (two cells): 2001, 2003
'''Why''': Returns both the MIN and MAX SDMX periods from the root grid sheet.

---------------------------------------------------------------------------------------------------------
===== Error: sheet not found =====

=SDMXPeriod("NonExistantRootGrid",,TRUE)

'''Result''': Sheet Not Found
'''Why''': The specified sheet name doesn’t exist, so the function returns a “Sheet Not Found” error.

---------------------------------------------------------------------------------------------------------

=== Troubleshooting ===
===== MIN period isn’t the earliest in the grid =====

'''Why''': SDMXPeriod("…","MIN") returns the first period it finds in the grid header, which is typically (but not guaranteed to be) the earliest.

'''Fix''': Sort your period headers left→right so the earliest period is first.

---------------------------------------------------------------------------------------------------------
===== MAX period isn’t the latest in the grid =====

'''Why''': SDMXPeriod("…","MAX") returns the last period it finds in the grid header, which is typically (but not guaranteed to be) the latest.

'''Fix''': Sort your period headers left→right so the latest period is last.

---------------------------------------------------------------------------------------------------------
===== FXLDataLookup or SDMXPeriod behaves oddly in “vertical mode” =====

'''Why''': Both functions expect a horizontal grid (periods across columns) like the RootGrid examples; vertical layouts aren’t supported.

'''Fix''': Render the root grid with periods in the header row and values beneath them.

---------------------------------------------------------------------------------------------------------
===== Grid not found =====

'''Why''': The grid header (dimensions + period labels) must be on row 2 of the root sheet; otherwise the grid isn’t detected.

'''Fix''': Move headers to row 2 on the root sheet.

---------------------------------------------------------------------------------------------------------
===== Sheet not found =====

'''Why''': The sheet name you passed doesn’t match an actual worksheet.

'''Fix''': Use the exact sheet name that exists in the workbook.

---------------------------------------------------------------------------------------------------------
===== Key not found =====

Why: The series key is misspelled or doesn’t exist in the root grid’s Series column.
Fix: Copy–paste the key from the grid (avoid typos) and ensure the root grid includes full series keys.

---------------------------------------------------------------------------------------------------------
===== Expected multiple cells, but only one cell returned =====

'''Why''': Multi‑cell (“spilled”) results require a modern Excel (later than 2016) that supports dynamic arrays.

---------------------------------------------------------------------------------------------------------
===== Invalid power =====

'''Why''': The right‑hand side of ^ must be a numeric literal or a reference to a numeric cell; a series‑derived expression/key is invalid (see example using …^(EG1:EURO:GERMANY-26)).

'''Fix''': Replace the exponent with a number (e.g., ^2) or a cell that contains a number.

---------------------------------------------------------------------------------------------------------
===== Non‑numeric value in grid =====

'''Why''': The target cell in the root grid holds text (e.g., g), so numeric calculation fails.

'''Fix''': Correct the source cell to a number or point the formula to a numeric period.

---------------------------------------------------------------------------------------------------------
===== Divide by zero =====

'''Why''': Your denominator evaluates to 0 (e.g., EG1:EURO:GERMANY - 26 = 0).

'''Fix''': Choose a non‑zero reference.

---------------------------------------------------------------------------------------------------------
===== Parse error =====

'''Why''': The items/expression string has a syntax error (operator typo, or missing parenthesis).

'''Fix''': Check operators and parentheses; compare against a working example and re‑enter carefully.

===== Operations on multiple reference cells =====
Require using ampersands and quotes.
'''Why''': When performing operations like summing or multiplying without ampersands and quotes, Excel evaluates the referenced cells before passing the value to FXLDataLookup, which results in an incorrect input for the formula.

'''Fix''': Use ampersands and quotes, e.g. FXLDataLookup(E1&"+"&F1;D1;"דוגמאות";FALSE;TRUE)

This ensures the first parameter is passed as a string and correctly processed by the formula.

@@ Line 12: / Line 12: @@
 New Formulas have been introduced:
-==FXLDataLookup ==
+'''''FXLDataLookup'''''
 [[FXLDataLookup_Function |Please refer to this article.]]

@@ Line 89: / Line 89: @@
 ---------------------------------------------------------------------------------------------------------
-===== FXLDataLookup or SDMXPeriod behaves oddly in “vertical mode” =====
+===== FXLDataXPeriod behaves oddly in “vertical mode” =====
 '''Why''': Both functions expect a horizontal grid (periods across columns) like the RootGrid examples; vertical layouts aren’t supported.
@@ Line 96: / Line 96: @@
 ---------------------------------------------------------------------------------------------------------
 ===== Grid not found =====

FXLDataPeriod Function - Revision history

Vmurrell: /* FXLDataLookup or SDMXPeriod behaves oddly in “vertical mode” */

Vmurrell: /* FXLDataLookup */

Vmurrell: /* FXLDataLookup Please refer to this article. XXXXXXX */

Vmurrell: Created page with "Category:Functions Reference FXLData Introduced in H1.3.0.03 (January 2026). =Background= == FXLDataPeriod Formulas == === Context === To date, FXLData has been used pri..."