Hi There,



I've having trouble with what appears to be some kind of glitch. The formulas in the linked spreadsheet (it's too big to attach, sorry) are not displaying the results of the formula, although the formula is definitely correct because the F9 evaluation yields the correct result. Can someone please take a look and see why this might be happening? I've googled across the web but no answers I've seen appear to solve the problem. As a clue, I've noticed that if I delete all the blue sheets (Kay Rd Subdivision (250) - Blackwood RoW (258), the problem is solved. Of course, though, I don't want to delete these sheets...

I'm also having a problem with circular references. Whenever I use the today() function, excel is deeming it a circular reference. Does anyone know why this might be?

https://drive.google.com/file/d/0B3g...ew?usp=sharing

Thanks for your help.


Nathan

The problems are all caused by the circular references, which are actually on the W1 to W5 sheets, in rows 28:30 in all the machine columns. Your COUNTIF ranges include the cells that the formulas are in, which is what causes all the problems. Removing the formulas from those cells fixes everything, though you presumably need to actually correct the formulas!

Thank you so much... I've been stumped by this for some time.

Can you please tell me how you managed to locate these circular references? My circular reference button was always grayed out in the error checking function, and the status bar was pointing me to all sorts of cells as a circular reference, some of which only tied back to a simple constant cell or a date. How were you able to locate the actual cause of the error?

A little bit of trial and error with some logic, to be honest. It looked like your Wx sheets were pretty much copies of each other so I deleted all but one of them to start with. Then when I deleted the last one, all the errors went away so I knew there was an issue on there. That was when the trial and error part came in - I just started going through the formula cells row by row until I noticed the problem.

By the way, if you're going to cross-post the same question in several forums, you're supposed to provide links in each one.

Thanks a lot. Regarding the links, sure, will do. Thanks for your help.