Forum Discussion
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 sheet. But it works when I try to use the same function in a new file.
The two screens attached are different files but all the rest (cell formatting...) seems to be the same.
Thanks for any help.
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.
5 Replies
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Here is the sample. Thanks!
(Google Docs already shows the formula with the "hidden" reference)
https://docs.google.com/spreadsheets/d/1NSgFYSHc-xUEeJUNg-lIH96rC79N6WO3/edit?usp=share_link&ouid=112964879561593668046&rtpof=true&sd=true
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.
