I'm trying to figure out a way to change this workbook in to something I can post (currently includes a lot of information that I can't), but in the meantime (or in case I can't), maybe someone has some more suggestions.
My workbook is multiple worksheets for several different parts of a project, with actuals and forecast data on each, and then several other worksheets that pull data from each of those in to different summary views.
Everything worked fine until a couple of months ago, when I started getting #N/A errors. The problem is, they don't seem to be real errors. If I go in to the formula bar for one of these cells, and change nothing, and then hit Enter, the cell calculates as it should.
I've found various threads and articles and have tried many suggestions. The big one was that maybe an XLOOKUP to a different workbook may be stalling/not completing the lookup, and so my workbook is confused as to what the value is. I removed all external lookups (in fact, at this point I have removed all external links), and it actually seemed to work for a few weeks, but then it came back.
So, here are some of these cells, including the formula.
Now, I've gone in to the formula bar for that first #N/A cell, changed nothing, and hit enter. And I get this.
So, the results of the cell aren't #N/A (in this case, it's 0, but there could also be a different non-zero number when this occurs).
Pressing F9 does not help. Pressing CTRL-ALT-F9 sometimes helps, but rarely. Doing a find-and-replace (replace = with =) was what was working the best for awhile, but even that has stopped working in all instances.
Which is fine, because I am not really trying to figure out how to force it to recalculate so the #N/As are replaced with the correct values - instead, I would like to know what is causing this and what I might be able to do in order to stop this going forward. The find-and-replace with the equals was a pain even when it worked, because I also couldn't do it across the whole workbook - I even had to do that sheet by sheet.
Has anyone run in to this before and might now what else I can check to find the source of this? I'm fine with a minor redesign if it means this goes away.
I am going to work on trying to get a version of my workbook that still has the issue that I can post, but it's going to be a big effort to make that happen. Thank in advance if anyone has any suggestions.
My workbook is multiple worksheets for several different parts of a project, with actuals and forecast data on each, and then several other worksheets that pull data from each of those in to different summary views.
Everything worked fine until a couple of months ago, when I started getting #N/A errors. The problem is, they don't seem to be real errors. If I go in to the formula bar for one of these cells, and change nothing, and then hit Enter, the cell calculates as it should.
I've found various threads and articles and have tried many suggestions. The big one was that maybe an XLOOKUP to a different workbook may be stalling/not completing the lookup, and so my workbook is confused as to what the value is. I removed all external lookups (in fact, at this point I have removed all external links), and it actually seemed to work for a few weeks, but then it came back.
So, here are some of these cells, including the formula.
Now, I've gone in to the formula bar for that first #N/A cell, changed nothing, and hit enter. And I get this.
So, the results of the cell aren't #N/A (in this case, it's 0, but there could also be a different non-zero number when this occurs).
Pressing F9 does not help. Pressing CTRL-ALT-F9 sometimes helps, but rarely. Doing a find-and-replace (replace = with =) was what was working the best for awhile, but even that has stopped working in all instances.
Which is fine, because I am not really trying to figure out how to force it to recalculate so the #N/As are replaced with the correct values - instead, I would like to know what is causing this and what I might be able to do in order to stop this going forward. The find-and-replace with the equals was a pain even when it worked, because I also couldn't do it across the whole workbook - I even had to do that sheet by sheet.
Has anyone run in to this before and might now what else I can check to find the source of this? I'm fine with a minor redesign if it means this goes away.
I am going to work on trying to get a version of my workbook that still has the issue that I can post, but it's going to be a big effort to make that happen. Thank in advance if anyone has any suggestions.
