CHILDCELLS outputting one value but expecting array

I am using the collection of hierarchy formulas and getting unexpected results. I’d like to explain it with a link to the spreadsheet. Is there a particular way to share it as a link or, if not, what email address should I add?

Thanks for reporting this @Adam_Steinfurth! We’ll take a look asap.

Feel free to share the workbook directly with me: matt@spreadsheet.com. If you make me a Manager and you don’t mind me sharing the workbook with other Spreadsheet.com team members I will do so.

FYI sharing folders and workbooks by link is a new feature coming soon. I.e it’s on our short term roadmap.

I have shared the spreadsheet with you.
So, it’s a list of dates that I made in a way that it makes sense to group them hierarchally. The top of the hierarchy is the year (2021) in cell B1. The second level of indentation are the months (Jan 2021) in cell B2 and (Feb 2021) in cell B7. The third level is the days (1/1/2021) in cell B3 and so on.

I used the indent feature on these and the following are the unexpected outcomes that I have experienced:

1 - I closed and reopened the spreadsheet and the grey boxed +/- indicators that show the indentations are now gone. The only indication of indentation that is left is that the indentation icon in the main menu is greyed out when cell B1 is active and black when cells B2:B10 are active.

2- In cell B12, I have used the ANCERSTORCELLS formula. I was expecting it to return an array of the values in cells B1 and B2 but instead is just returned the value in B1.

3- In cell C12, I was expecting the CHILDCELLS formula to return both child cells of that cell which I was thinking were B2 and B7, but instead it just returned the value in B2.

4 - In cell D12, I was expecting the CHILDCELLS formula to return all an array with all three child cells of that cell which I was thinking were B8:B10, but instead it just returned the value in B8.

Thanks again Adam, share received. We’re taking a look at this and will let you know what we find.

Hi Adam,

It looks like you ran into two issues, one a bug, and the other due to incomplete support for array syntax and array spillover functionality. Details:

Issue #1:

1 - I closed and reopened the spreadsheet and the grey boxed +/- indicators that show the indentations are now gone. The only indication of indentation that is left is that the indentation icon in the main menu is greyed out when cell B1 is active and black when cells B2:B10 are active.

Since you had column A set as the primary column, the +/- indicators appeared there when you did your indentations. However, there is a bug in Spreadsheet.com whereby a +/- indicator is not shown if the value in the primary column is empty. We will be fixing this and tracking it as issue # 003389. Meanwhile, setting the primary column to a column with values resolves the problem – so setting it to column B shows the indicators as expected.

Issues 2, 3, and 4:

2- In cell B12, I have used the ANCERSTORCELLS formula. I was expecting it to return an array of the values in cells B1 and B2 but instead is just returned the value in B1.

3- In cell C12, I was expecting the CHILDCELLS formula to return both child cells of that cell which I was thinking were B2 and B7, but instead it just returned the value in B2.

4 - In cell D12, I was expecting the CHILDCELLS formula to return all an array with all three child cells of that cell which I was thinking were B8:B10, but instead it just returned the value in B8.

These functions do return arrays of values but since we don’t yet support array spillover capability (values spill over into adjacent columns and rows) and our support for array syntax is incomplete, you are only seeing the first value. If you used CONCATENATE(CHILDCELLS()) you would see multiple values combined together, but our support for date values in this scenario also needs work :slight_smile:

With a number or currency column, you will get the expected behavior when using aggregation functions like SUM in combination with hierarchy functions (I’ve added this to your worksheet to demonstrate):

Hope this helps explain the unexpected results you are seeing. Issue #1 should be addressed in short order. Issues 2, 3, and 4 will take a bit longer. We’ll update this thread as progress is made, and once again – thanks so much for the feedback!

Hi Adam,

ISSUE#1 - I closed and reopened the spreadsheet and the grey boxed +/- indicators that show the indentations are now gone. The only indication of indentation that is left is that the indentation icon in the main menu is greyed out when cell B1 is active and black when cells B2:B10 are active.

This has been resolved with the latest product update on 31/03/2021.

Will update you further on other issues once they are resolved.

Thanks

2 Likes