data tables & index/match function

Brass Contributor

As of yesterday, when I create a data table within a Excel 365 file & use that table within an index/match function, when I hit enter, the formula is looking for an external source & never had to do that before.  when I ignore, I then get a NA or a #spill! error.  Then when I save the file & reopen it, it is trying to update an external link yet there are none anywhere in that file except for the newly created data table.  

 

If someone would be able to get me out of this nightmare, it would be greatly appreciated.  I have attached a file

16 Replies

@rservice0320 

Hello! You've posted your question in the Tech Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future. 

thank you. I couldn't figure out how to do that. & upload a file.

so, this is still an open issue. I don't know why it auto clicks on best response. Should I repost my question?

I reposted my question as apparently when I thanked someone for putting it into the correct community, interpreted as a response, so unfortunately, I am still in the data table & index/match function quagmire.

 

As of yesterday, when I create a data table within a Excel 365 file & use that table within an index/match function, when I hit enter, the formula is looking for an external source & never had to do that before.  when I ignore, I then get a NA or a #spill! error.  Then when I save the file & reopen it, it is trying to update an external link yet there are none anywhere in that file except for the newly created data table.  

 

If someone would be able to get me out of this nightmare, it would be greatly appreciated.  I have attached a file.

In the drop down menu next to your comment (the ...) you would have selected "best response" by accident.

If you are 100% certain you didn't do that, I can reach out to our tech lead to indicate that in case there's a bug.

If this happens again, you can also remove "best response" through the same menu.

I have removed the "best response" marking from your post.

@rservice0320 

I selected File > Info.

I saw this near the lower right corner:

S1397.png

I clicked on 'Edit Links to Files'.

S1398.png

I clicked 'Break Link', then clicked Close.

When I saved, closed and reopened the workbook, the message didn't appear.

@Hans Vogelaar Hello all! I wasn't aware you (@rservice0320) had re-posted the question and to avoid duplicates, I have merged these two threads. Sorry for any confusion!

except if you break the link, the formula is changed into a value which is what I don't want to happen. I still need the index match formula to work. So, unfortunately, breaking it doesn't solve my issue.
I do not see a drop down menu to remove this "best response".

@rservice0320 Hello! Here's the image of how you remove a best response.

 

Of course, only the author of the original post (such as yourself in this case) can mark best response or remove it, along with MVPs and employees.

 

I hope that helps for the future!

 

Not-best-response.png

@rservice0320 

The formulas in B6 and B7 on the KPI Dashboard sheet make no sense. For example

 

=INDEX(Table1!$C$2:$E$17,MATCH(1*(Table1!$A$2:$A$17=$A$7)*(Table1!$B$2:$B$17=$A$5)*(Table1!$C$1:$E$1=$B$5),0))

 

refers to a worksheet named Table1, but there is no such sheet. There is a table of that name, but you cannot refer to it that way.

But even if you replace Table1 with 'kpi Table' or 'kpi Database', the formulas won't return a useful result.

@rservice0320 

See the attached version. I hope the formulas in it do what you want. you'll see that they are structured differently from what you had,

well, this formula results in a spill error:
=INDEX('kpi Database'!$C$2:$E$17,MATCH(1,'kpi Database'!$A$2:$A$17=$A$7)*('kpi Database'!$B$2:$B$17=A$5)*('kpi Database'!$C$1:$E$1=$B$5),0)
is there some reason why the formula won't read the years in KPI database (in the rows C1:E1) vs. everything else is in columnar form. I would think the formula should be able to find it & match it. maybe I need to include another index so it reads the rows? The resulting value should be 250 for "CDF Vol for 2019". Sorry this is causing such agita, but I am been working on this for couple of hours.


@rservice0320 

Did you take the trouble to look at the workbook that I attached?

I apologize, I did not see that attachment, I was too focused on my spreadsheet. I hadn't seen that way to create a formula for the rows. Thank you for your time.