SUM formula doesn't work (Summing range calculated by INDIRECT formula)

[VojtechSima]

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?

[#2]

Hi,

Do you have any circular references anywhere?

Regards

[#3]

@VojtechSima 

I'm taking a wild guess: There is a circular reference in the worksheet.

[#4]

@JosWoolley 

How can I get that?

My B column is calculated like this:

=INDEX(SheetNames;A2)

How can I identify the circular references?

[#5]

@Detlef_Lewin 

Hello, as I replied to @JosWoolley .

[#6]

@VojtechSima 

I did test for Circular references but it seems greyed out in that sheet:

@Detlef_Lewin @JosWoolley 

[#7 · Accepted solution]

@Detlef_Lewin @JosWoolley 

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-b...