I have a #REF error on one PC but not another
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:
- Both computers are using the same version of Excel (same build number too: Version 2406 Build 17726.20126).
- We have the same add-ins running on both computers.
- We have checked Options > Advanced > For objects, show: All and it is selected on both PCs
- We have uninstalled and reinstalled Office.
- 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.
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!
