question: #NAME? problem

[areqq]

Question

my formula

 =LET(
      lid_list, ANCHORARRAY(A2),
      row_count, ROWS(lid_list),
      headers, G1:R1,
      col_count, COLUMNS(headers),
      sums, SUMIFS(THours[hours], THours[lid], INDEX(lid_list, SEQUENCE(row_count, 1)), THours[arve_type], INDEX(headers, 1, SEQUENCE(1, col_count))),
      sums
  )

after opening xlsx in excel 365 I got

but edit, cell without any change fix and formula works.

[jmcnamara]

The issue here is that Excel adds metadata prefixes to some newer functions. XlsxWriter tries to add these where possible but it doesn't attempt to parse formulas so sometimes the end user needs to modify the formula to match Excel.

Have a look at all the sections of https://xlsxwriter.readthedocs.io/working_with_formulas.html

Since you are already using ANCHORARRAY you may already be aware that some functions need prefixes and in this example I don't see any that aren't handled automatically.

If you upload the file I can take a look and tell you what is wrong. Otherwise you can try the debug instructions here and look at the formula in the Excel output file.

https://xlsxwriter.readthedocs.io/working_with_formulas.html#dealing-with-formula-errors

[areqq]

I found a solution, this works fine:

_xlfn.LET(
      _xlpm.lid_list,_xlfn.ANCHORARRAY(A2),
      _xlpm.row_count, ROWS(_xlpm.lid_list),
      _xlpm.headers, H1:S1,
      _xlpm.col_count, COLUMNS(_xlpm.headers),
      _xlpm.sums, SUMIFS(THours[hours], THours[lid], INDEX(_xlpm.lid_list, _xlfn.SEQUENCE(_xlpm.row_count, 1)), THours[arve_type], INDEX(_xlpm.headers, 1, _xlfn.SEQUENCE(1, _xlpm.col_count))),
      _xlpm.sums )

[jmcnamara]

Good work. Thanks for letting me know.

In general XlsxWriter fills in the _xlfn prefixes but the _xlpm prefixes need user additions.