Forum Discussion
data tables & index/match function
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 ...
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.
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.
- 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.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.