Sheet formulas show "#VALUE!". After F2 in any cell and then "enter", all cells show calculated value: what's wrong?

[fabiospark]

Let me expand a little.

Calculation is on "Auto".
Max Iterations is = 1
Accept labels in formulas is = 1

I'm trying to create a dynamic table on Sheet1 to use a data source for a chart and I'like to select the categories and series headers from validated dropdown boxes.

Sheet1 has a few cells with various formulas:

Q413 Validation is on to let me choose from a list of names of named ranges, from a dropdown box. I.e: Assets

Q414 [="Dash_"&$Q$413] concatenation to build the name of an existing named range. I.e: Dash_Assets

Q418 Validation is on to let me choose from a list of names of named ranges, from a dropdown box. I.e: Currencies

Q416 [="Dash_"&$Q$418] concatenation to build the name of an existing named range. I.e: Dash_Currencies

U418:AJ418 in each cell of this range there is a formula to get the # name in the chosen name list i.e: Assets (index 1 = Insurance, 2 = real estate, and so on)
These values becomes the column headers of my dynamic table. The formula looks like this:
=IF((COUNT.IF($U$418:U$418;"<>"""))<=ROWS(INDIRECT($Q$413));INDEX(INDIRECT($Q$413);COUNT.IF($U$418:U$418;"<>"""));"")

Q419:Q436 [in each cell of this range there is a formula to get the # name in the chosen name list i.e: Currencies (index 1 = CHF, 2 = EUR, and so on)
These values becomes the row headers of my dynamic table. The formula looks like this:
=IF((COUNT.IF($Q$419:$Q419;"<>"""))<=ROWS(INDIRECT($Q$418));INDEX(INDIRECT($Q$418);COUNT.IF($Q$419:$Q419;"<>"""));"")

U419:AJ436 [in each cell of this range there is a formula to get the sum of the values that satisfy the two criteria set by the headers of the column and the row that define the cell address
i.e: in cell U419 it should sum all the values of the records that has "Insurance" in their "Assets" column and "CHF" in their "Currency" column
The formula it's a matrix one as follows:
{=SUM(IF(INDIRECT(myindirect($Q$416))=$Q419;IF(INDIRECT(myindirect($Q$414))=U$418;Dash_TdyValues)))}

R419:R436 [in each cell of this range there is a formula to get the sum of the values of the same row.
For R419 the formula is: (U419:AJ419)

R416 [=SUM(R419:R436)]

S419:S436 [in each cell of this range there is a formula to get the percentage of the sum value on the left cell of the same row against the total sum of cells R419:R436.
For S419 the formula is: (R419/$R$416).


This is the strange behaviour I'm experiencing:

when I select Sheet1 the values cells [U419:AJ436] show "#VALUE!" and the sum cells [R419:R436] and the % cells [S419:S436] show the values they were showing the last time I exit Sheet1.
I select an empty cell, hit F2 and then ENTER, and now all the values cells [U419:AJ436] show the correct result of their formulas. The sum cells [R419:R436] and the % cells [S419:S436] show "#VALUE!".
I again select an empty cell, hit F2 and then ENTER, and now all the sum cells [R419:R436] show the correct values but the % cells [S419:S436] still show "#VALUE!".
I again select an empty cell, hit F2 and then ENTER, and now all the % cells [S419:S436] still show the correct values.
Now I select different row headers i.e: Area instead of Currencies and all the values cells [U419:AJ436] show the new correct result of their formulas but the sum cells [R419:R436] and the % cells [S419:S436] still show the previous values.
Now I again select an empty cell, hit F2 and then ENTER, and now all the sum cells [R419:R436] and the % cells [S419:S436] show the correct values again.

Then I select another sheet, hit F9, go back to Sheet1 and the all the values cells [U419:AJ436] show "#VALUE!" again. The sum cells [R419:R436] still show the previous values.
Everything goes again as the steps above.

If I go to another sheet and don't do nothing, when I go back to Sheet1 the correct values are still shown both by value and sum cells.

I tried these things and saw some improvement:

Max iteration = 2
With this calculation setting I still get the "#VALUE!" error shown by all the values cells [U419:AJ436] when I activate Sheet1 after having hit F9 on another sheet,
but after the firts "select empty cell --> hit F2 --> hit Enter" sequence, the correct values appear in both the values and the sum cells.
The % cells still show "#VALUE!" that goes away after the next F2 sequence.
If I change the row headers all the values, sum and % gets updated.

Max iteration = 3
With this calculation setting I still get the "#VALUE!" error shown by all the values cells [U419:AJ436] when I activate Sheet1 after having hit F9 on another sheet,
but after the firts "select empty cell --> hit F2 --> hit Enter" sequence, the correct values appear in all the values, sums and % cells.
If I change the row headers all the values, sum and % gets updated.

To make the calculations execute automatically when I select Sheet1, I added two lines ov VBA in the "Worksheet_Activate" section of Sheet1:

Range("P413").Select
ActiveCell.FormulaR1C1 = ""

In this way, when I select Sheet1 I can see the table full of "#VALUE!" errors but after half a second everything works as expected.

So, it seems I managed to fix this problem. But...

but I'm concerned about the fact that some calculation were executed but some others didn't.
To me this is scary because in this way you can never be sure if you are getting the correct values or not.

In the status bar, no matter how many times I hit F9, I always see the indication "Calculate".

Does anybody know what's happening and why? And how to avoid this problem?

Thanks.

 

[Legacy 456129]

It's indeed in the iteration issue. Possibly you've got some circular references. The Formula Ribbon has option to research those CR's. If you click Error Checking and Circular References is gray, than you can tick off that as an issue.