Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 10-04-2017, 04:52 PM
nath1235 nath1235 is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2017
Posts: 5
nath1235 is on a distinguished road
Default Formula result not displaying in cell (but f9 works)

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
Reply With Quote
  #2  
Old 10-05-2017, 04:47 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 135
Debaser is on a distinguished road
Default

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!
Reply With Quote
  #3  
Old 10-05-2017, 12:14 PM
nath1235 nath1235 is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2017
Posts: 5
nath1235 is on a distinguished road
Default

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?
Reply With Quote
  #4  
Old 10-06-2017, 12:36 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 135
Debaser is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 10-06-2017, 12:42 PM
nath1235 nath1235 is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2017
Posts: 5
nath1235 is on a distinguished road
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula, 2 criteria, one result cjdstephenson Excel 4 06-02-2015 09:17 PM
Perform calc in active cell then highlight based on formula result grexcelman Excel Programming 4 01-12-2015 11:00 AM
Show result of formula as a value in another cell Steve_D Excel 4 10-12-2014 07:38 PM
IF formula returns wrong result mashley Excel 3 09-07-2012 07:03 AM
Can I create a formula that will show result in the same cell and let me copy it down CranstC Excel 1 02-11-2012 01:29 AM


All times are GMT -7. The time now is 03:46 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft