Dynamic arrays across workbooks

Dynamic arrays are indeed powerful, but their behavior across workbooks does have some important limitations, even though they may appear to work fine in many situations.

Scenario                                                                                            Works When Source Workbook is Closed?

Regular functions like XLOOKUP, INDEX, etc. referencing ranges                                          Yes

Referencing a cell with a spilled dynamic array (e.g., =A2#)                                                  No

Referencing named ranges that are defined by dynamic arrays                                            No

Copy-pasted values from a spilled array                                                     Yes (but not dynamic anymore)

If you need dynamic arrays across workbooks and the source may be closed, consider:

• Converting the source data into named ranges or tables and referencing those with traditional formulas.
• Or, copying the dynamic array output as static values periodically via Power Query, VBA, or manual update.

Hope this helps :-)