Forum Discussion
I have a #REF error on one PC but not another
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 https://answers.microsoft.com/en-us/msoffice/forum/all/has-anyone-noticed-xlpq-calc-errors-in-excel-when/7a3ed92e-faf5-4ba9-bb7a-67fc7cfcd3dd.
I went back and updated the name of all of my queries to be unique, and everything is now working!
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!!
Sharing this thread: https://answers.microsoft.com/en-us/msoffice/forum/all/excel-formulas-suddenly-stopped-working/9e3eafb2-ef7b-46a2-9d94-358dcd594916
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."
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.
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 https://answers.microsoft.com/en-us/msoffice/forum/all/has-anyone-noticed-xlpq-calc-errors-in-excel-when/7a3ed92e-faf5-4ba9-bb7a-67fc7cfcd3dd.
I went back and updated the name of all of my queries to be unique, and everything is now working!