#REF! Issue when updating children including formula

Hi there team!
I am working with a spreadsheet in which I am using SUM(CHILDREN()) to add up budget items into a larger categories.

One of the budget items is not just a number, but is a formula that results in a number. When that formula updates, it seems to break the SUM(CHILDREN()), causing a #REF! error that I can solve only be re-entering the SUM formula. Not sure if this lies in SUM or in CHILDREN, but wanted to bring it up!

Thanks!

As of now, we are not supporting circular references which might have caused the #REF! error. Could you please remove the formula cell which you mentioned and try whether that resolves SUM(CHILDREN()) formula.
This will be addressed soon, as this is on our roadmap.

Rob, I’ll have the concerned dev respond to this in short order. He may need more details from you. It may well be a case of circular ref or not. In any case - spreadsheet needs better visual error reporting/feedback capabilities and we are working on those. I thank you for your patience.

Rob, we believe there is a possible circular reference in your sheet, hence, #REF! evaluation error. Request you to share the other formula details as well apart from =SUM(CHILDREN()).

Suspecting a scenario something like below. As highlighted, B5 cell CHILDREN() list contains [B6,B7]. However, B7 formula cell again references B5 (which is its parent).

image

Due to this both formula cells evaluate as #REF!

image

2 Likes

Thanks for all of this! I think it was a circular ref issue, but strangely rendered different ways at different times!

It was a SUMIF that intentionally attempted to exclude the cell that would have caused the circular issue. That formula seemed to always evaluate correctly (no REFS) but the SUM that included it seemed to give the issue.

I created a work-around for now, but if we want to explore further later, I’m down!

Yes, for SUMIF any cell in match-range that has evaluation errors, will only fail match-condition (i.e. not be considered for aggregation) but not fail the formula.

However, in case of SUM(), any referenced cell with evaluation errors will fail the formula always & consistently. If you are seeing any different behavior for SUM() request you to share more details for us to investigate further.