Formulae Do Not Calculate

[BobHunter]

Very strange behaviour...

I do have my calculations set to automatic, I am not opening any other workbooks which have this set to manual, and I have even tried code to recalculate the cells.

My formula is

="A"&(164+COUNTA(Plan!A164:A188))

The strange thing is, this WORKS for possible 6 iterations, and then refuses to work. Is there some stack limit I am reaching or something ?

I can copy the EXACT formula into another cell and this calculates correctly, the one above only recalculating when I choose the formula again, and hit Enter.

You thoughts appreciated.

 

[combo]

Hi Bob,
I guess that you have circular reference - see at excel status bar.
If it is so and you did it intentionally - check settings in Tools>Options>Recalculation (or similar) and iteration settings. If not - do not use as arguments ranges with output cell, even if in theory this should not have influence on final result, this will be always treated as circular with all its consequences; so move your formula outside range in argument.

combo

 

[BobHunter]

Hi Combo,

Checked, and there no circular references, in fact, there are no formula or references in the range (Plan!A164:A188) as this hold purely text values.

I suppose I should try this way to get around the problem - I need to find the next unused cell in the range A164:A188.

Range("A164:A188&quot.End(xlDown).Offset(1, 0).Select

This works but still doesn't tell me why my calculations did not update !!

 

[combo]

Bob, if you like someone to see at it - feel free to send mkb@op.pl

combo

 

[Zathras]

...My formula is ="A"&(164+COUNTA(Plan!A164:A188))...

What exactly do you expect to happen? When the range is finally filled up (all cells from A164 to A188 are used), the formula will give the result "A189" no matter what else you do. There are only 25 cells in the range and 25 + 164 equals 189.

The formula works exactly as you wrote it. Now, if you changed it to something along the lines of

  ="A"&(164+COUNTA(Plan!A164:A65536))

perhaps you will get results more to your liking.

 

[BobHunter]

Nope

My problem was that if cells A164 to A168 were filled the result would be A169. Correct.

If in the next iteration, cells A164 to A169 were filled, the result of the formula would still remain as A169. Incorrect.

?

 

[RobBroekhuis]

Sounds like you're trying to do things in worksheet cells that would be more appropriately handled in VBA code (and I think your earlier post indicated you've already moved in that direction).
Rob