FXLDataLookup and FXLDataPeriod Functions - Revision history

Vmurrell: /* Parse error */

2026-04-20T17:22:46Z

‎Parse error

Vmurrell: /* FXLDataLookup Examples */

2026-01-26T09:33:23Z

‎FXLDataLookup Examples

Vmurrell: /* FXLDataLookup Examples */

2026-01-25T10:33:43Z

‎FXLDataLookup Examples

Vmurrell: Vmurrell moved page FXLData - New Functions to FXLDataLookup and FXLDataPeriod Functions

2026-01-23T07:43:29Z

Vmurrell moved page FXLData - New Functions to FXLDataLookup and FXLDataPeriod Functions

Vmurrell at 07:40, 23 January 2026

2026-01-23T07:40:39Z

Vmurrell: /* FXLDataPeriod Examples */

2026-01-23T07:38:31Z

‎FXLDataPeriod Examples

Vmurrell: /* FXLDataPeriod Examples */

2026-01-23T07:28:58Z

‎FXLDataPeriod Examples

Vmurrell: /* FXLDataPeriod Examples */

2026-01-23T07:27:34Z

‎FXLDataPeriod Examples

Vmurrell: /* FXLDataPeriod Examples */

2026-01-23T07:24:53Z

‎FXLDataPeriod Examples

Vmurrell: /* Period helpers (using SDMXPeriod(...) inside) */

2026-01-23T07:23:10Z

‎Period helpers (using SDMXPeriod(...) inside)

← Older revision		Revision as of 17:22, 20 April 2026
Line 379:		Line 379:

	'''Fix''': Check operators and parentheses; compare against a working example and re‑enter carefully.		'''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 85: / Line 85: @@
 [[File:Example-spreadsheet.png|1000px]]
-[[:File:FXLDataLookupSamples-V1.zip | Example Spreadsheet]] Download and extract from the zip file to obtain an Excel version of the spreadsheet.
+[[:File:Example-Spreadsheet-V2.zip | Example Spreadsheet]] Download and extract from the zip file to obtain an Excel version of the spreadsheet.
 ==== Basics ====

@@ Line 84: / Line 84: @@
 [[File:Example-spreadsheet.png|1000px]]
 ==== Basics ====
@@ Line 265: / Line 267: @@
 ---------------------------------------------------------------------------------------------------------
 === FXLDataPeriod Examples ===
 The examples below are based on the following root grid sheet.

← Older revision		Revision as of 07:40, 23 January 2026
Line 1:		Line 1:
		+	[[Category:Functions Reference FXLData]]
	Introduced in H1.3.0.03 (January 2026).		Introduced in H1.3.0.03 (January 2026).

← Older revision		Revision as of 07:38, 23 January 2026
Line 299:		Line 299:
	'''Result''': Sheet Not Found		'''Result''': Sheet Not Found
	'''Why''': The specified sheet name doesn’t exist, so the function returns a “Sheet Not Found” error.		'''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.

← Older revision	Revision as of 07:43, 23 January 2026
(No difference)

@@ Line 269: / Line 269: @@
 [[File:Root-grid.png|1000px]]
-'''Minimum period (single value)'''
+===== Minimum period (single value) =====
 =SDMXPeriod("RootGrid","MIN")
@@ Line 277: / Line 277: @@
 ---------------------------------------------------------------------------------------------------------
-'''Maximum period (single value)'''
+===== Maximum period (single value) =====
 =SDMXPeriod("RootGrid","MAX")
@@ Line 285: / Line 285: @@
 ---------------------------------------------------------------------------------------------------------
-'''Range (min and max — two‑cell spill)'''
+===== Range (min and max — two‑cell spill) =====
 =SDMXPeriod("RootGrid")
@@ Line 293: / Line 293: @@
 ---------------------------------------------------------------------------------------------------------
-'''Error: sheet not found'''
+===== Error: sheet not found =====
 =SDMXPeriod("NonExistantRootGrid",,TRUE)

← Older revision		Revision as of 07:27, 23 January 2026
Line 275:		Line 275:
	'''Result''': 2001		'''Result''': 2001
	Why: Returns the earliest (minimum) SDMX period available on the root grid sheet.		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.

← Older revision		Revision as of 07:24, 23 January 2026
Line 267:		Line 267:
	The examples below are based on the following root grid sheet.		The examples below are based on the following root grid sheet.

−	[[File:Root-grid.png\|~~100.px~~]]	+	[[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.

← Older revision		Revision as of 07:23, 23 January 2026
Line 262:		Line 262:

	'''Result''': Parse Error		'''Result''': Parse Error
		+
		+	---------------------------------------------------------------------------------------------------------
		+	=== FXLDataPeriod Examples ===
		+	The examples below are based on the following root grid sheet.
		+
		+	[[File:Root-grid.png\|100.px]]