Forum Discussion
SUM formula doesn't work (Summing range calculated by INDIRECT formula)
Hello guys,
I have dynamic list of SheetNames.
Then I have couple of columns where I for each sheet calculate certain formula with INDIRECT function inside of it to refer to a sheet.
Like this:
=COUNTIF(INDIRECT(B2 &"!$L$2:$L$33"); "Something")
The whole sheet look like this (sorry for the czech language but it's not relevant to the issue):
Then I'd like to sum up every column, simply like this:
=SUM(C2:C14)However, it returns 0. Even tho I formatted it as Number or I also tried to add VALUE formula etc, nothing really works and I have no clue.
Could someone help me how I can sum the columns?
So I fixed by enabling the Iterative Calculations:
Inspired by this article: https://support.microsoft.com/en-us/office/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-bcf7-1b49cd50d123
Hi,
Do you have any circular references anywhere?
Regards
How can I get that?
My B column is calculated like this:
=INDEX(SheetNames;A2)How can I identify the circular references?
Hello, as I replied to JosWoolley .
I did test for Circular references but it seems greyed out in that sheet:
So I fixed by enabling the Iterative Calculations:
Inspired by this article: https://support.microsoft.com/en-us/office/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-bcf7-1b49cd50d123
