Possible bug, Groups, Indirect. Cell moves. Identified but why?
If this is a bug, I don’t know how to submit a bug report. Can someone please submit a bug report? If not, maybe someone can explain what’s happening.
To recreate issue..
(iPhone) iOS Numbers spreadsheet.
Step by step to create issue.
• Create spreadsheet.
• 1 table, no headers, exactly 16 rows.
(note I have 4,000 rows, just making this simple)
• Add 3 groups to table.
• Place 10 of 16 rows in top group (needs to exceed number of rows in lower groups)
• Place 3 of 16 rows in middle group.
• 3 of 16 rows should be left in bottom group.
(layout part finished)
• In top group place formula in to cells B1:B7
INDIRECT(ADDRESS(11,1))
INDIRECT(ADDRESS(12,1))
INDIRECT(ADDRESS(13,1))
INDIRECT(ADDRESS(14,1))
INDIRECT(ADDRESS(15,1))
INDIRECT(ADDRESS(16,1))
INDIRECT(ADDRESS(17,1))
• Cells B8:B10 can be left empty.
Now cells B1:B7 will mirror “location” of cells A11:A17.
(formulas part finished)
• In middle group fill cells A11:A13 with any unique values.
(values part finished)
It’s now ready to produce the possible bug.
I’m using Numbers on iPhone iOS 17.3.1
• Take notice where top group mirrored values appear, B1:B3.
• Tap and hold on middle group, slide middle group down until middle group switches position with bottom group, release finger. Now bottom group is middle and middle group now at the bottom. Or group with typed in vaues now in rows 14-16 where as before they switched position previously located in rows 11-13. The objective was to move group of rows with values to a different position.
That’s it! Let’s see what happened? On my phone the values in top section are STILL showing in B1:B3. The values even update in B1:B3!
You might expect this result if using formulas in top group,
=A11
=A12
=A13
=A14
=A15
=A16
=A17
But I didn’t use these formulas, I used,
INDIRECT(ADDRESS(11,1))
INDIRECT(ADDRESS(12,1))
INDIRECT(ADDRESS(13,1))
INDIRECT(ADDRESS(14,1))
INDIRECT(ADDRESS(15,1))
INDIRECT(ADDRESS(16,1))
INDIRECT(ADDRESS(17,1))
Did you get the problem too? Are the values still showing in top group cells B1:B3? Or did the values shift to B4:B6 where I believe they should be?
It gets even more weird. Assuming values are still showing in B1:B3, look at the formulas in B1:B3.. ARE the formulas pointing to the original locations A11:A13, but showing values for A14:A16? That’s weird, right?
Did this happen for you or is it just me? Imagine how this messes up data big time!
OK BUT WAIT there’s more! Go to the group menu (top right button from app) and turn the table’s group off, turn group back on. Look again at top group, notice now values appear correctly in cells B4:B6 where they belong?
This is an ongoing issue for me. I have to turn groups off and back on every time I modify a group where location changed. As long as toggle off and back on groups after modifying, everything works fine moving forward. It doesn’t seem to update on its own, only updates when I turn it off and back on. Even if I close the app, swipe up to remove it from memory and reload the app nothing changes in top group until I turn off groups and back on. This is a workaround to keep my spreadsheet working.
How about you same thing? The reason I used indirect with address was to prevent formula from follow values.
Anyone else having similar issue can follow the same steps turning off tables groups then turning groups back on after every group modification triggering an update. I think it only affects mirrored formulas, as most other formulas are supposed to follow when linked. I don’t think offset and indirect are supposed to follow? Please correct me if I’m wrong, but if I am wrong, then turning groups off and back on, should not cause the reset? Which should it be following values or not following values it does both depending on if I turn groups off and back on, very inconsistent to be something intentional?
