SOLVED

Can't fix #REF! with INDEX

Copper Contributor

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.

 

Screenshot 2024-03-27 at 15.14.59.png

 

Screenshot 2024-03-27 at 15.22.44.png

 

5 Replies

@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.

 

Screenshot 2024-03-27 at 16.41.18.png

 

@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?

@Hans Vogelaar 

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

best response confirmed by bertofivee (Copper Contributor)
Solution

@bertofivee 

Thanks! The table on the INDEX sheet has been named Index:

HansVogelaar_0-1711557738381.png

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)

HansVogelaar_1-1711557908587.png

Switch to the second sheet. You'll see that the formula uses the edited name:

HansVogelaar_2-1711557968876.png

Change it to =INDEX(A1:A3,1). The formula will then work as intended.

HansVogelaar_3-1711558032286.png

Solved. Thank you so much, Hans!
1 best response

Accepted Solutions
best response confirmed by bertofivee (Copper Contributor)
Solution

@bertofivee 

Thanks! The table on the INDEX sheet has been named Index:

HansVogelaar_0-1711557738381.png

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)

HansVogelaar_1-1711557908587.png

Switch to the second sheet. You'll see that the formula uses the edited name:

HansVogelaar_2-1711557968876.png

Change it to =INDEX(A1:A3,1). The formula will then work as intended.

HansVogelaar_3-1711558032286.png

View solution in original post