Working on a formula involving TEXTJOIN and OFFSET, may have found a bug

Question

Level 1

8 points

Working on a formula involving TEXTJOIN and OFFSET, may have found a bug

Hi everyone. As the title suggest, I'm working on a formula with TEXTJOIN and OFFSET. This is what the 'working' formula looks like before the issue is introduced:

On the left is a table of dates that has a Month Category (there are other months below, just saving on image real estate here). VERY important to note that that table has a category, as the issue does not arise in tables without a category. Table 2 is just the TEXTJOIN function on its own to show it doesn't appear to be the culprit here. Table 3 is TEXTJOIN + OFFSET.

The Issue: Whenever I add in a row, the value of that row does not appear in the TEXTJOIN + OFFSET formula. See the image below (differences marked for your convenience):

Table 3 basically does not acknowledge that row's existence. Even if I delete the 'old' 1/3/25 row, it still won't read the new one. Changing the 'height' parameter of OFFSET doesn't work (just skips right over it), adding in extra columns and changing the width parameter doesn't do anything, etc. This is NOT an issue for tables that have NO categories. At this point, I'm wondering if there's something I'm missing or if this is a legitimate bug. I'd appreciate any help whatsoever.

Windows, Windows 10

Posted on Apr 2, 2024 9:10 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

31,268 points

Apr 3, 2024 3:15 AM in response to AmandaOn

Sounds familiar. See what happens if you uncategorized the table. It should update all those functions. Then you can recategorize.

Unless it was fixed in version 13.2 (which I am not able to upgrade to), but I doubt it was, there are problems with INDIRECT, OFFSET, and INDEX when referencing categorized tables that have anything structurally done to them such as adding/deleting rows, moving rows around manually, or sorting. The formulas will not update to the new structure if it is a categorized table.

There is also a problem with FORMULATEXT when trying to look at a formula that is referencing a categorized table. My test was to have a simple reference to a categorized table, like =Table 1::B5. I sorted the categorized table and the formula followed cell B5 to its new location (as it should) becoming Table 1::B4 but FORMULATEXT continued to say the formula was Table 1::B5.

I reported the bug with INDIRECT and FORMULATEXT to Apple in June 2023. The others functions were found later but I didn't bother reporting them.

Reply

Answer 2

AmandaOn Author

Level 1

8 points

Apr 3, 2024 10:28 AM in response to Badunit

>Sounds familiar. See what happens if you uncategorized the table. It should update all those functions. Then you can recategorize.

That seems to be the magic ticket. Uncategorizing, then recategorizing finally gets OFFSET to recognize the new rows. It's pretty frustrating having to resort to that, but at least it's a quicker and easier fix than the monstrosity I was planning. Thanks ✌️

Reply