Forum Discussion
Can't fix #REF! with INDEX
- Mar 27, 2024
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.
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.
- bertofiveeMar 27, 2024Copper ContributorSolved. Thank you so much, Hans!