Forum Discussion
Can't fix #REF! with INDEX
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.
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.
- Solved. Thank you so much, Hans!