I generate an excell sheet using Apache POI including 3 defined names used as the data for a graph.

This all works very nice except in a wierd edge case.

I have a column Categories where the user can enter text to categorize related data.
In my example there are 5 rows and if the user inputs two different categories, no more and no less on the 5 rows then this anomaly happens for a few combinations but not all otherwise everything works as expected.

The Name NumberOfCat counts the number of different categories in the categories column (starting at F13 and ending at F17).

NumberOfCat = SUMPRODUCT(1/COUNTIF(Data!$F$13:$F$17;Data!$F$13:$F$17))

Then there is the name CategoryNames that should list all categories
H13 – H17 contain working formulas so as to list one of the categores on every row or “” of there are no more unique categories.

CategoryNames = OFFSET(Data!$H$13;0;0;NumberOfCat;1)

If I enter the categories
everything works. If I change it to It does not.

Now for some reason entering CategoryNames into a cell evaluates to only “Kat1” instead of “Kat1, Kat2”. This while entering NumberOfCat into a cell still evaluates to 2 as it should.

Entering the CategoryNames formula into a cell and replacing NumberOfCat with 2 generates the correct result.

I suspect some very weird execution order is going on but have no idea how to proceed. I’m using Microsoft® Excel® for Microsoft 365 MSO (Version 2404 Build 16.0.17531.20152) 64-bit and the error reproduces on Excel for the web. If you want to have a look at the example file I can provide it. If any more context is needed please ask.