Can't fix #REF! with INDEX

[bertofivee]

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.

[#2]

@bertofivee

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.

[#3]

@bertofivee 

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?

[#4]

@HansVogelaar 

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=11...

[#5 · Accepted solution]

@bertofivee 

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.

[#6]

Solved. Thank you so much, Hans!