#N/A Error, but formula is valid

NCSUAaron

New Member
Joined
Jun 17, 2010
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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.

1684181129806.png


Now, I've gone in to the formula bar for that first #N/A cell, changed nothing, and hit enter. And I get this.

1684181189970.png


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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
I am not sure, Please try changing "Bid Rate Sheet'!$B:$B" and"Bid Rate Sheet'!$AK:$BI" to the exact cell range like for eg: "B1:B10"
 
Upvote 0
I am not sure, Please try changing "Bid Rate Sheet'!$B:$B" and"Bid Rate Sheet'!$AK:$BI" to the exact cell range like for eg: "B1:B10"
Thanks for the suggestions. I changed that, as well as some other cells that were set up similarly.

But the problem persists. At a glance, it seems as though maybe it's fewer cells that are reverting to #N/A before I force fix them, but I'm not sure. I also have not completely narrowed down what causes them to flip from "correct" to #N/A. I know it happens when I run a Macro that cycles through all of the tabs and pulls info from several over to a summary sheet. But, it also happens other times when I haven't run that MACRO.

1684255233604.png


So the cell I have highlighted above is showing #N/A. The picture below shows the results after just entering the formula bar, changing nothing, and hitting Enter.

1684255298323.png
 

Attachments

  • 1684255212916.png
    1684255212916.png
    21.4 KB · Views: 6
Upvote 0
Please try changing to "INDEX('Bid Rate Sheet'!$AK2:$BI3000")"
 
Upvote 0
Please try changing to "INDEX('Bid Rate Sheet'!$AK2:$BI3000")"

Thank you again!

I made that change. Note that I also had to change the first part of the XLOOKUP to "XLOOKUP($Z7:$Z82,'Bid Rate Sheet'!$B2:$B3000". After doing that, and re-running the Macro that usually seems to cause the #N/As to show up, everything stayed valid! I'm going to work in the file normally for a few days and see if the issue pops up again, and then I'll report back.

I sort of see what this change did, but would love to understand more fully. The INDEX/MATCH combo is being used to determine which column to use for RETURN_ARRAY in the XLOOKUP. But, the array for the INDEX function overlapped with the header column, which contains a month/year and is what I was using MATCH for to pull from the correct matching column on the Bid Rate Sheet. So, having the INDEX array overlap with the header row array from the MATCH column seems (assuming the issue doesn't pop back up) to have been the culprit. Should those 2 arrays always be mutually exclusive?

In retrospect, after digesting this, I don't really know that I even need this Index/Match approach to determine the XLOOKUP column. I did it that way originally just to make sure I was always pulling the correct column, but in reality the layout of the data on the Bid Rate Sheet is always going to be in the same order as on this sheet, so I could just go in order and save this mildly complicated formula. If it turns out that the problem does pop back up, that's what I'll try next.

I'll report back!
 
Upvote 0
What happens if you put 0 in the if missing position of your XLookup ?

1684322709701.png

PS: Please update your profile and let us know what version of Excel you are using.
Also I can't be more specific in the above because you have only provided an image of your formula not your actual formula.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top