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!
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!