Forum Discussion
Solved
Can't fix #REF! with INDEX
Hi all, I need help to get rid of this #REF! when using the INDEX function. I have two Excel files with some sheets; in both, I get an error when I try to use the INDEX function, even with a new ...
Thanks! The table on the INDEX sheet has been named Index:
So when you use Index in a formula, Excel thinks you refer to the table, but then you cannot use it as a function.
You can rename the table:
- Click in any cell of the table on the INDEX sheet, for example cell A5
- Activate the Table Design tab of the ribbon.
- Edit the name in the Table Name box (see screenshot)
Switch to the second sheet. You'll see that the formula uses the edited name:
Change it to =INDEX(A1:A3,1). The formula will then work as intended.
Found what looks like to be the issue. When I click on trace error it shows a reference to a different workbook which I have never put there plus, it is not possible to delete.
In this screen, I am working on "Sheet2" and the reference is to "INDEX". I tried to delete it but it is not possible.