SOLVED

I have a #REF error on one PC but not another

Copper Contributor

We have a workbook with no external links. On my PC there are no errors. On another user's PC we get #REF errors.

 

Formula setup: In one cell we have a a Data Validation dropdown list with the source setup as =INDIRECT(Named_Range) that refers to a column in a table. In this cell, the dropdown list does not work because of a #REF error. When I look in Name Manager, I can see the correct data referenced for the named range in the "Value" column (there is no #REF error here). In another cell we have another dropdown list set up exactly the same way referring to a different table that works without errors. 

 

What we've tried:

  1. Both computers are using the same version of Excel (same build number too: Version 2406 Build 17726.20126).
  2. We have the same add-ins running on both computers.
  3. We have checked Options > Advanced > For objects, show: All and it is selected on both PCs
  4. We have uninstalled and reinstalled Office.
  5. We have updated both Office and Windows, but the problem persists. 

Any thoughts on what I could try next? Thanks! I'm pulling my hair out over this one.

4 Replies

@reeves1685 I'm having the SAME issue using Data Validation on Indirect function! It's happening to some users but not all, and we all have the same version of excel.  Help!!

@reeves1685 

 

Sharing this thread: https://answers.microsoft.com/en-us/msoffice/forum/all/excel-formulas-suddenly-stopped-working/9e3ea...

 

The first step here seemed to have fix my file and hope it does the same for you!

1. Function Disabled or Not Recognized: • Ensure that the OFFSET and INDIRECT functions are not disabled in your Excel settings. Go to "File" > "Options" > "Formulas" > "Working with formulas" and make sure "Enable Iterative Calculation" is checked. • Also, ensure that you have not accidentally disabled the "Analysis ToolPak" add-in, which contains functions like AVERAGE. You can enable it by going to "File" > "Options" > "Add-Ins."

 

@ExcelRookie4529 

 

Thanks for sharing that link. The team tried your suggestions and those in the link and it did not work.

I created a sample file  with mixed results in the team. One user said "Neither dropdown works for me in the Sample File." Another said "They both worked for me in the sample file. After checking the sample files I went to the [original file] I was having trouble in, and the Enable Iterative Calculation and Analysis Toolpack were both off. I turned them on and am still having trouble with the [original file]. Only in the "Site Prep" table though."

 

What's weird about the issues is that this started with one user when I first posted this message a few days ago, and now all three regular users of the file are having the issue. Not only with this particular file but even the simple example file. And each are having the issue in different ways.

 

best response confirmed by reeves1685 (Copper Contributor)
Solution

Ok, I found a solution!

 

As I kept digging there was a clue that I did not include in the sample file: the tables behind the named ranges are populated by Power Query. I tried deleting and recreating the queries. It worked for one named range, but not the rest. As I was trying to further narrow down the issue, I saw that when I tried referencing the named range there was a new duplicate named range with an _xlpq prefix. Searching that up I found this post.

 

I went back and updated the name of all of my queries to be unique, and everything is now working!

1 best response

Accepted Solutions
best response confirmed by reeves1685 (Copper Contributor)
Solution

Ok, I found a solution!

 

As I kept digging there was a clue that I did not include in the sample file: the tables behind the named ranges are populated by Power Query. I tried deleting and recreating the queries. It worked for one named range, but not the rest. As I was trying to further narrow down the issue, I saw that when I tried referencing the named range there was a new duplicate named range with an _xlpq prefix. Searching that up I found this post.

 

I went back and updated the name of all of my queries to be unique, and everything is now working!

View solution in original post